Benjamin W Benjamin W - 6 months ago 11
SQL Question

Mysql SELECT row NOT IN AND GROUP together

I have a timetable with 2 tables

//user table
id name
1 ben
2 alex
3 billy
4 john

//schedule table
year month date id
2016 5 15 2
2016 5 22 1 // id 1 - ben want to swap his time with others
2016 5 23 2
2016 5 30 3
2016 5 31 1
2016 5 22 3


id 1 - ben wants to swap his schedule with other's ppl.

I need to SELECT name FROM user JOIN schedule and GROUP BY name

than I need those user name don't have any schedule at 2016-5-22

//output should be like this
id name
2 alex //he have schedule at 15, 23
4 john //he don't have any schedule


billy and ben have schedule at 22, so they don't show up

I try to write something like

SELECT name FROM user LEFT JOIN schedule ON user.id = schedule.id
WHERE year = 2016 && month = 5 && date != 22


but this query will still show up billy, because billy have a row in 2016-5-30

Is anyone can help me with this query

Answer

You need to filter groups not rows for your query to work.Simple guide WHERE for rows HAVING for groups

SELECT name FROM user LEFT JOIN schedule ON user.id = schedule.id
GROUP BY name
HAVING SUM(year = 2016)>0
AND SUM(month = 5)>0
AND SUM(date=22)=0
Comments