CODEWITHSUNDEEP
mysqlsqlpartitioning
Robert
Robert

Reputation: 61

SQL query to find a specific value for all records

Col1;Col2;Col3
12345;01;Y
12345;02;Y
12345;03;Y
22222;01;Y
22222;02;Y
22222;03;N
33333;01;N
44444;01;Y

Need help in writing a SQL query to find all the records with value = 'Y' based on col1.For Eg output select Col1 should give the output as 12345 and 44444 [ not 22222 and 33333 as the col3 contains 'N' for them ]

Thanks a lot for your time

Upvotes: 0

Views: 196

Answers (4)

ditch
ditch

Reputation: 92

my solution using the difference

select distinct col1 from demo where col1 not in 
(select col1 from demo where col3='N')

Upvotes: 1

sorineatza
sorineatza

Reputation: 106

My solution:

select distinct col1
from mytable
where col3='Y'
and col1 not in (select distinct col1 from mytable where col3='N')

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521914

One simple approach uses conditional aggregation:

SELECT Col1
FROM yourTable
GROUP BY Col1
HAVING MAX(Col3) = MIN(Col3) AND MAX(Col3) = 'Y';

Upvotes: 1

M Khalid Junaid
M Khalid Junaid

Reputation: 64476

I guess you need col1 where all values of col3 should be Y

select col1
from demo
group by col1
having count(*) = sum(Col3 = 'Y')

Demo

Or if there can be only 2 possible values for col3 like Y/N then you can simplify your having clause as 

having sum(Col3 = 'N') = 0

Demo

Upvotes: 2

Related Questions