Help with Count and mulitple tables

Hi all

I want to do a count from 2 tables. that match work.post_cat_id and categories.cat_id

table 1 = categories
table 2 = work

Currently I’m using this query to show all the categories that match each work type/

$SQL=mysql_query("select categories.*,  work.* 	From  categories, work Where 	categories.cat_id =  work.post_cat_id Group By cat_name	 Limit 55");

but now I want to know how many work(post_cat_id) match the categories(cat_id)

I want to know how many work(post_cat_id) match the categories(cat_id)

So you have the categories.cat_id value? If so aren’t you just selecting from a single table?


SELECT fields,needed FROM work WHERE post_cat_id = 356

Sorry I think I didn’t explain well, the thing is I want to get the field cat_seo_url

that is found on the categories table

I still don’t know what the result set you are trying to get should look like.

Do you want each seo_url and a count of how many work rows correspond to each?

basically I want to display from the categories table, cat_id,cat_seo_url

and from the work table, Where categories.cat_id = work.post_cat_id

this is an example, the number from the brackets are the number of matching work types found in the work table(post_cat_id)

the marketing work, accounting work, sales work, are all from the categories table

Marketing work (5)
Accounting work (2)
sales work (19)

etc…

and when I click on lets say sales work, I want the link to be cat_seo_url(found in the categories table)

SELECT categories.cat_id
     , categories.cat_seo_url
     , COUNT(work.post_cat_id) AS work_types
  FROM categories 
LEFT OUTER
  JOIN work
    ON work.post_cat_id = categories.cat_id 
GROUP
    BY categories.cat_id