Lannister Lannister - 6 months ago 9
MySQL Question

how to get user from sql who fulfill following these criteria in mysql

I am trying to get users from mysql database if they have created a note with

weather type = 'cloud','rain','clear'
and not those who have missed even one out of these three.

eg.

id user_id weather_type
1 12 cloud
2 12 rain
3 12 clear
4 14 rain
5 15 clear


now here only user 12 have created notes with all three
weather_type
so only 12 should be fetched not 14 and 15.

Answer

Use group by and having:

select   user_id 
from     mytable 
where    weather_type in ('cloud','rain','clear')
group by user_id
having   count(distinct weather_type) = 3
Comments