CODEWITHSUNDEEP
mysqlsql
Evd
Evd

Reputation: 31

select query in selection

I can't find a correct query for this next problem. (see image)

enter image description here

Hereby this table Schedule where day 1 is Monday, day 2 is Tuesday, .. I have to find out which teacher is free on Monday during the first hour but is present at school because he/she has to teach later on that day. So the result may only give John and Suzan, but not Mark and Tom because they don't have to teach that day.

I tried simple query and nested queries but none give the correct result.

select distinct Teacher from Schedule where Day = 1 and Hour <> 1 order by Teacher  ASC  

select distinct Teacher from Schedule where Day = 1 and NOT Hour = 1 order by Teacher  ASC

More complex queries don't make any difference.

What am I doing wrong?

Upvotes: 2

Views: 53

Answers (1)

sticky bit
sticky bit

Reputation: 37472

In that special case it might be possible to use aggregation. Check if the minimum hour of the day of the teacher is greater than 1.

SELECT teacher
       FROM schedule
       WHERE day = 1
       GROUP BY teacher
       HAVING min(hour) > 1;

A more general approach for any day and any hour might use conditional aggregation. Only count, if the teacher teaches a specific hour at the day. So, when the count is 0, we know they don't teach at the hour given.

SELECT teacher
       FROM schedule
       WHERE day = ?
       GROUP BY teacher
       HAVING count(CASE
                      WHEN hour = ? THEN
                        1
                    END) = 0;

(Replace the ? with the values for the day or the hour, respectively.)

Yet another approach is to use NOT EXISTS and a correlated subquery.

SELECT DISTINCT s1.teacher
       FROM schedule s1
       WHERE s1.day = ?
             AND NOT EXISTS (SELECT *
                                    FROM schedule s2
                                    WHERE s2.day = s1.day
                                          AND s2.teacher = s1.teacher
                                          AND s2.hour = ?);

If you have a table with all teachers, you could also use that as a source and filter by EXISTS (for the day) and NOT EXISTS (for the day and hour) targeting schedule.

Upvotes: 3

Related Questions