Select lines that does not include null in one column-CodePudding

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

CodePudding user response：

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)

CodePudding user response：

SELECT client, count(credit)  FROM t
group by client
having count(client) = sum(case when credit is null then 0 else 1 end)