CODEWITHSUNDEEP
sqlsql-serversql-server-2012
Simsons
Simsons

Reputation: 12735

How select values where all columns are null for particular ID, ID is not unique

I have a table with following format and I want to get the LotId if Value1 is null for all the rows.

Now If I am doing Select, Select * from Table1 where Value1 IS null , I am getting back a row .

But I want nothing should be returned as there are two rows which have some value.

I thought of self join , but this can have n number of rows.

Id          LotId       Value1      
-------------------------------------------------
1       LOt0065          NULL       
2       LOt0065          SomeValue
3       LOt0065          SomeValue

Upvotes: 0

Views: 361

Answers (1)

bbrumm
bbrumm

Reputation: 1352

I think you'll need to use an EXISTS subquery here:

SELECT a.lotid
FROM table1 a
WHERE NOT EXISTS (
  SELECT 1
  FROM table1 b
  WHERE b.lotid = a.lotid
  AND b.value1 IS NOT NULL
);

If my syntax is right, then this will show you all records that don't have any NULL values for that lotid:

  • It uses a SELECT 1 because the subquery doesn't need to show any value, it just needs to match on the outer query.
  • You compare the table in the inner query to the table in the outer query and match on the common field you're looking at (lotid in this case)
  • This could also be done with a NOT IN clause.

Does this give you the result you want?

Upvotes: 1

Related Questions