So guys, trying to write a query to get the count of statuses where project_id = ? and statuses in 'New' from a couple of tables so let me break it down.
I have these three tables
id case_status_id project_id application_id
1 1 1 20
2 2 1 21
SELECT COUNT(referrals.id) AS count_all, case_statuses.case_status AS counted
FROM "case_statuses" LEFT OUTER JOIN "referrals" ON "referrals"."case_status_id" = "case_statuses"."id"
WHERE "case_statuses"."deleted_at" IS NULL AND (case_statuses.case_status IN ('New') AND referrals.project_id = 1)
GROUP BY case_statuses.case_status;
Conditions on the second table (in a
left join) should be in the
SELECT COUNT(r.id) AS count_all, cs.case_status AS counted FROM case_statuses cs LEFT OUTER JOIN referrals r ON r.case_status_id = cs.id AND r.project_id = 1 WHERE cs.deleted_at IS NULL AND cs.case_status NOT IN ('New') GROUP BY cs.case_status;
WHERE clause turns the outer join into an inner join.