Lannister Lannister - 2 years ago 79
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.


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
so only 12 should be fetched not 14 and 15.

Answer Source

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
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download