CODEWITHSUNDEEP
sql
FrankTan
FrankTan

Reputation: 1686

Sql Query select all rows not in another set

Suppose I have this table: 

sett_tb_plant_trans

(pk)uidplanttrans  field1  stoptime
---------------------------------   
x                  1       1/1/2000
y                  1       null 
z                  2       1/1/2000
k                  2       1/1/2000
v                  3       null
j                  4       null

I would like to select just z and k because them have the stoptime filled for all the rows and same "key" for the field1.

i did this query :

 select 
   field1 from sett_tb_plant_trans 
 where uidplanttrans not in 
   (select uidplanttrans from sett_tb_plant_trans where stoptime is null)

but returns some field1 that have some rows with stoptime null

why?

Upvotes: 2

Views: 3003

Answers (2)

Klaus Byskov Pedersen
Klaus Byskov Pedersen

Reputation: 121057

You should be able to simplify your query like this:

select field1 from sett_tb_plant_trans where stoptime is not null

It should definitely give the right results. There are however rows in your table where the same value for field1 has a stoptime that is null, for example the rows with PK x and y have the same value 1 for field1, but for y stoptime is null.

If you only want those values of field1 where there is no other row with the same field1 value that has a stoptime that is null, you can do it with the sub-select approach, just slightly different:

 select 
 field1 from sett_tb_plant_trans 
 where field1 not in 
 (select field1 from sett_tb_plant_trans where stoptime is null)

This would give you 2 rows, because it is true for the rows with PK z and k. Depending on your needs you might want to select distinct(field1) from ....

Upvotes: 2

Tom Studee
Tom Studee

Reputation: 10452

I'm confused as to why you are using a nested query.

Wouldn't

select field1 from sett_tb_plant_trans where stoptime is not null

work?

Upvotes: 0

Related Questions