How to use an sql subquery that uses count.

Question

I have a system where reservations are stored as records in one table(workshop_reservation) and sessions (the things being reserved) are stored in another table(session). Each reservation belongs to a session, and each session belongs to a workshop.

I want to make a mysql query that finds the number of reservations for each session that belongs to a certain workshop. I was trying to use count() to find the number of reservations in a given session, and this query works fine for that purpose (# is the session number):

SELECT count( r.id )
FROM workshop_reservation AS `r`
WHERE r.session_id = #

I want to figure out how to perform this query for every session. I can easily find every session_id using the following query:

SELECT s.id
FROM session AS `s` 

My problem is combining these, because when any of the sessions have no reservations, no row is printed for that session. Instead, I want it to print a 0 in the count column for sessions that have no reservations. The following query works fine for sessions that have at least one reservation, but it ignores sessions that have no reservations (doesn't print a row saying 0).

SELECT count( r.id ) , r.session_id
FROM workshop_reservation AS `r`
WHERE r.session_id
IN (
SELECT s.id
FROM session AS `s`
WHERE s.workshop_id =1
)
AND r.reservation_status_id =0
GROUP BY r.session_id

How can I force it to print a row with a 0 in it in this situation?

Answer 1

You need to use a JOIN

For instance

SELECT COUNT( r.id ) , r.session_id
FROM workshop_reservation r
LEFT JOIN sessions s ON r.session_id = s.id
AND r.reservation_status_id =0
GROUP BY r.session_id

Answer 2

You don't need a subquery. You can use a simple GROUP BY which will return 0 when there are no reservations for the given session, or the number of reservations.

SELECT s.id, count( r.id )
FROM session AS 's' LEFT JOIN workshop_reservation AS `r`
ON s.id=r.session_id
GROUP BY s.id

Answer 3

I thought I should put this here instead of choosing one of your answers, since neither of them quite worked alone. I used elements from both (mostly mdma with one line from nico). Here is the query that does exactly what I want:

SELECT session.id, count( workshop_reservation.id )
FROM session LEFT JOIN workshop_reservation ON session.id = workshop_reservation.session_id
AND workshop_reservation.reservation_status_id =0
GROUP BY session.id

I don't know how obvious this is to SQL experts, but if the AND is changed to WHERE, the reservation_status_id is actually ignored.

Answer 4

select s.*, count(wr.id) as num from sessions as s 
  left join workshop_reservation as wr on wr.session_id = s.id
group by s.id