Getting multiple counts across joined tables
I have 3 related tables:
business
id
name
inspection
id
business_id
date
violation
id
inspection_id
title
I'd like to get out:
[business.id, Count(Inspections), Count(Violations)]Where Count(Inspections) is the total number of inspections this busi开发者_JS百科ness has had and Count(Violations) is the total number of violations across all inspections that this business has had.
I can do one or the other in a single query but I'm not sure how to get both in one shot.
SELECT b.id, COUNT(i.id)
FROM inspections_business b, inspections_inspection i
WHERE
b.id = i.business_id
GROUP BY b.id
SELECT b.id, COUNT(v.id)
FROM inspections_business b, inspections_inspection i, inspections_violation v
WHERE
b.id = i.business_id
AND i.id = v.inspection_id
GROUP BY b.id
Use OUTER joins:
SELECT b.id,
COUNT(i.id) AS num_inspections,
COUNT(v.id) AS num_violations
FROM BUSINESS b
LEFT JOIN INSPECTION i ON i.business_id = b.id
LEFT JOIN VIOLATION v ON v.inspection_id = i.id
GROUP BY b.id
Your queries use ANSI-89 join syntax, where the join criteria is in the WHERE
clause. It's been superseded by ANSI-92 syntax. There's no performance benefit, but OUTER join syntax is consistently supported, when each database vendor had their own OUTER join syntax for ANSI-89.
精彩评论