Mohammad Mohammad - 5 months ago 12
SQL Question

MYSQL: how to return 0 on count foreign key, if its not exist

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

Answer
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