I am working on small school database and I need some help with selecting students that are only booked on different days in week 1 and week 2.
My table structure is like below:
ChildId Day Room Week ======= === ==== ==== 1 1 20 1 1 2 20 1 2 1 20 2 3 1 20 1 3 2 20 1 3 1 20 2 ===== === === ===
I would like to get all the children that meet the following criteria:
- ONLY booked in week two (This is ChildId 2 and I can get this from the following query)
SELECT DISTINCT b.childid FROM booking b where b.childid NOT IN (SELECT childid FROM bookingtemplate WHERE weekno = 1)
I also need
- all children in week two whose days are different then week one. That will be ChildID 3 as he is booked on Monday and Tuesday on Week 1 and on Monday on week 2.
I am unable to get the children whose days are different in both the weeks:
Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.
You can self-join the table and filter the cases where days are different.
SELECT DISTINCT b1.ChildId FROM booking b1 INNER JOIN booking b2 ON b1.ChildId = v2.ChildId AND b1.Week = 1 AND b2.Week = 2 AND b1.Day <> b2.Day
You can do it with aggregation and the conditions in the
SELECT ChildId FROM booking WHERE Week IN (1, 2) GROUP BY ChildId HAVING MIN(Week) = 2 OR GROUP_CONCAT(DISTINCT CASE WHEN Week = 1 THEN Day END ORDER BY Day) <> GROUP_CONCAT(DISTINCT CASE WHEN Week = 2 THEN Day END ORDER BY Day);
See the demo.
All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0