retrieving ids with 'only' one type of code

  • A+
Category:Languages

Sample of table:

ID     Code 2324    1 2324    2 2325    1 2326    1 2326    2 

I want to get the id’s that only have code ‘1’ and not also code ‘2’ so the result would be

2325    1 

Since the others have code’s 1 and 2

I've tried

SELECT * FROM TABLE WHERE CODE != 1 AND CODE = 2 

but that just returns any id's with code 2 regardless if the id also has code 1 or not

 


With NOT EXISTS:

SELECT * FROM TABLE T WHERE T.Code = 1 AND NOT EXISTS (   SELECT 1 FROM TABLE WHERE ID = T.ID AND Code <> T.Code ) 

Comment

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: