Count over two SQL tables
A bit of SQL that saved my butt lately: Have you ever needed to relate a value from one table to the count of another value in another table? Given you have two tables, one for a timetable, the other for tickets, and you want to find out how many tickets are sold for each tour in the timetable, the following will give it to you (tours.tour_id is related to tickets.ticket_tour_id, who'd have thought of that):
MySQL:
-
SELECT DISTINCT tour_id, COUNT(ticket_tour_id)
-
FROM tours
-
RIGHT JOIN tickets
-
ON tour_id = ticket_tour_id
-
GROUP BY ticket_tour_id
-
ORDER BY departure;
