MYSQL Select different records from same table

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

Answers:

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.

Method 1

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

Method 2

You can do it with aggregation and the conditions in the HAVING clause:

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