Group by number value based on second value

[djmousie]

I am trying to extract items in Field 1 based on a criteria in Field 2. For Example, I want the query to only pull back data on the record 1234 since that record has no "Y". However, if I dont want record 123 and 12345 to appear since if you group those numbers, there is a "Y" and an "N", I only want those unique records where there is an "N" only.

Field1 Field2
123 Y
123 N
123 N
1234 N
12345 Y
12345 N

Any help?

 

[PHV]

Something like this ?
SELECT Field1
FROM yourTable A
WHERE Field2 = 'N'
AND Not Exists(SELECT * FROM yourTable B WHERE B.Field1=A.Field1 AND B.Field2='Y')

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[PHV]

Another way:
SELECT Field1
FROM yourTable
GROUP BY Field1
HAVING Max(Field2) = 'N'

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[djmousie]

Thanks to both of you. This worked!