paulducsantos paulducsantos - 7 months ago 9
SQL Question

How can I return a MySQL query with rows that have no counts in a joined table?

I have 2 tables (Passages and Questions where questions has a foreign key of Passages)

I have a query as follows:

SELECT p."id", p."passageTitle", COUNT(q."PassageId") as numQuestions
FROM "Passages" as p
LEFT JOIN "Questions" as q
ON p.id=q."PassageId"
WHERE q."status" = 'active'
GROUP BY p.id
ORDER BY p.id ASC


My goal was to get the count of "questions with status = active" on another column but if that "passage" has no "questions with status = active" to be 0.

My problem is that this query only returns rows that have a q."status" = 'active' but I still want to include the rows that do not meet this criteria. What do I add to my query that will make this possible?

Answer

Join only Questions with status = active

SELECT p."id", p."passageTitle", COUNT(q."PassageId") as numQuestions
FROM "Passages" as p 
LEFT JOIN "Questions" as q 
ON p.id = q."PassageId" AND q."status" = 'active' 
GROUP BY p.id 
ORDER BY p.id ASC