JOIN & COUNT

[PeteBWhat]

Hi there,

I have several tables: programs, accommodations, events, and products. I'm trying to pull all of the columns out of the programs table, but also get a count to see how many accommodations, events and products each program has. I came up with this, but it's not working quite right. If anyone has any insight, it would be greatly appreciated.

			 SELECT p.*, COUNT(a.accommodation) AS accommodation_count, COUNT(e.event) AS event_count, COUNT(t.product) AS product_count
			 	FROM programs as p
				LEFT JOIN accommodations AS a ON (p.program = a.program)
				LEFT JOIN events AS e ON (p.program = e.program)
				LEFT JOIN products AS t ON (p.program = t.program)
				WHERE p.program = '$program' GROUP BY p.program

[tomhath]

I assume the counts are too high, because the joins are done before the group by. Try it this way:

	SELECT p.*, a.accommodation_count, e.event_count, t.product_count
	FROM programs as p
	LEFT JOIN (SELECT program, count(*) as accommodation_count 
                 FROM accommodations GROUP BY program) AS a 
         ON (p.program = a.program)
	LEFT JOIN (SELECT program, count(*) as event_count 
                 FROM events GROUP BY program) AS e 
         ON (p.program = e.program)
	LEFT JOIN (SELECT program, count(*) as product_count 
                 FROM products GROUP BY program) AS t 
         ON (p.program = t.program)
	WHERE p.program = '$program'