Trouble with populating data on two mysql tables

Please help.

I have two tables.

subcat

id, user_id, subcat_id, title, description, price, best_offer, image, zip, premium_listing, status, date_create,d date_modified

and

aditem

id, user_id, subcat_id , title, descriptio,n pric,e best_offer image, zip, premium_listing, status, date_created, date_modified

I am trying to list all the categories in subcat and count the number of ad items that have been posted into aditem that relates to id in subcat

so results could look like this

auto - 7

junk cars -9

clothing - 10

bikes - 0

I have tried LEFT JOIN but it did not work. is there are way to accomplish this? Would UNION work?

here is an example of what i want to accomplish: http://www.ksl.com/index.php?nid=13

Here is what i have tried

   SELECT  subcat.id, subcat.cat_id, subcat.subcat, subcat.description AS subdesc, COUNT(aditem.subcat_id) AS itemc 
     FROM subcat
LEFT JOIN aditem  
       ON aditem.su开发者_StackOverflowbcat_id = subcat.id

thanks a million Earnest

---

You need to use a COUNT(*) statement in there. Something like

SELECT subcat.title, COUNT(aditem.id) AS count FROM subcat LEFT JOIN aditem ON subcat_id = subcat.id 

You might need this at the end; not sure and can't test right now:

GROUP BY aditem.id

---

Try to use

SELECT subcat.id, subcat.cat_id, subcat.subcat, subcat.description AS subdesc,    COUNT(aditem.id) AS itemc FROM subcat LEFT JOIN aditem ON aditem.subcat_id = subcat.id
GROUP BY subcat.cat_id