I have these two tables:
flights: id, ...
client_flights: id, idFlight, ...
I want to count foreach flight how many reservations were done on it (how many occurrences of the flight id in the table client_flights).
So, if the flight has no reservations, the count should return null.
here is my try:
SELECT IFNULL(COUNT(cf.id), 0) AS reserved, f.id
FROM flights f, clientflights cf
WHERE f.id = cf.idFlight
GROUP BY f.id
SELECT IFNULL(COUNT(cf.id), 0) AS reserved, f.id FROM flights f LEFT JOIN clientflights cf ON f.id = cf.idFlight GROUP BY f.id