How do you choose the employee who has worked the most night shifts of all time? There are 2 tables, one with workers, the second with night shifts, in which 2 people work per shift.
Users:
| id | name |
|---|---|
| 1 | Oliver |
| 2 | Harry |
| 3 | Jacob |
Hours:
| id | NightShift1 | NightShift2 |
|---|---|---|
| 1 | 1 | 3 |
| 2 | 2 | 2 |
| 3 | 3 | 1 |
| 4 | 3 | 2 |
| 5 | 2 | 2 |
| 6 | 1 | 2 |
| 7 | 1 | 3 |
| 8 | 3 | 1 |
>Solution :
To do this you can to essentially loop over the hours table twice; you do this by joining an ad hoc table specifying which shift you are looking at:
select users.id, users.name
from hours
join (select 1 position union all select 2) position
join users on users.id=if(position=1,hours.NightShift1,hours.NightShift2)
group by users.id
order by count(*) desc
limit 1