Select lines that does not include null in one column

user_12774556 0
Viewed 38

I want to select clients only when all the lines in Credit are filled. From the table I want to see only the lines for Client 1, Client 3, Client 4 and Client 5.

I was trying this:

SELECT *
  FROM table
 WHERE EXISTS (SELECT client FROM table WHERE [CREDIT] = 'cred')

But not working...

Thanks!

Client Credit
1 CRED
1 CRED
1 CRED
1 CRED
1 CRED
2
2 CRED
2 CRED
3 CRED
3 CRED
3 CRED
3 CRED
4 CRED
4 CRED
5 CRED
6
6
6
6 CRED
6 CRED
tsql sql-server
2 Answers

You need a self-(antisemi)join for this:

SELECT t.*
  FROM table t
 WHERE NOT EXISTS (SELECT 1
                     FROM table t2
                    WHERE t1.client = t2.client
                      AND t2.credit IS NULL)
user_58259630 
SELECT client, count(credit)  FROM t
group by client
having count(client) = sum(case when credit is null then 0 else 1 end)
shahkalpesh_235740
Related