MYSQL Select different records from same table-CodePudding

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

CodePudding user response：

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

CodePudding user response：

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.