CODEWITHSUNDEEP
sqlsql-server
John
John

Reputation: 13

SQL Query Help - Explanation Below

Stuck on how to query this result set.

A       B       C
162     0       NULL
162     1       NULL
162     2       NULL
163     0       000
163     1       000
163     2       000
164     0       000
164     1       NULL
164     2       NULL

I want to show only these results

A       B       C
162     0       NULL
162     1       NULL
162     2       NULL
164     0       000
164     1       NULL
164     2       NULL

I do not want to return results if all of an unique instance of column A has a value. So, in this example the new result set has removed 163 from the result set as all rows containing 163 had a value (Which was 000). The others show because one instance of the row contains NULL, and all rows unique to it should display.

I did try nested queries and playing around with GROUP BY, but haven't been able to crack it.

Any advice?

Thank you.

Upvotes: 0

Views: 31

Answers (1)

jarlh
jarlh

Reputation: 44766

Use EXISTS to find those rows:

select A, B, C
from tablename t1
where EXISTS (select 1 from tablename t2
              where t1.A = t2.A
                and t2.C IS NULL)

Or do a JOIN:

select A, B, C
from tablename t1
  JOIN (select DISTINCT A
        from tablename
        where C IS NULL) t2 ON t1.A = t2.A

Upvotes: 1

Related Questions