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.
我的目标是在另一列中获得“带有状态的问题=活动的”的计数，但是如果该“段落”没有“带有状态的问题=活动的问题”为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?
我的问题是，这个查询只返回具有q的行。“status”=“active”，但我仍然希望包含不满足此条件的行。我在我的查询中添加了什么才能使这成为可能?
1 个解决方案
#1
2
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
#1
2
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