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 |
2
Answers
You can UNION the
Hours
table on top of itself and then group by the user id to see who has the highest count:If you need the name, you can just INNER JOIN to that table in that outer
FROM
clause and pull that column through.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: