Count from Multiple Tables

[esport]

Hi Guys,

I have a table of categories. I want to display all the categories.

 

I also want to display how many times they appear in 2 other tables. I could do 2 more queries to count the number of times they appear, but i was wondering if i could do this in 1 query so I could order them asc and desc by the number of times they appear in other tables.

 

Thanks

 

Daniel

[cags]

I haven't tested it, but I think you'd want something along the lines of...

 

SELECT cats.name, COUNT(table1.cat_id) AS count_tabel1, COUNT(table2.cat_id) AS count_table2 FROM cats
JOIN table1 ON table1.cat_id=cats.id
JOIN table2 ON table2.cat_id=cats.id
GROUP BY cats.id

[esport]

Thanks for your help. I will have a go and let you know how i went.

 

Daniel

[esport]

Unfortunately it didn't work. It produces the same count value for both tables. It seems like it produces the highest count value.

Thanks

Daniel

[esport]

I managed to work it out. I did sub queries.

eg.

 

SELECT c.id as client_id,
					( SELECT COUNT(p.client_id) 
					FROM projects p
					WHERE p.client_id = c.id
				  ) AS num_projects,
				  ( SELECT COUNT(o.client_id) 
					FROM client_opportunity o
					WHERE o.client_id = c.id
				  ) AS num_opportunities
				  FROM client c