Getting multiple counts across joined tables

Question

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 business 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

Answer 1

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.