[musiccreatures]

I am having an issue writing the SQL statement to retrieve all of the category information from the category table as well as a count of ads within each category. I want to be able to add this feature to the ranking.php script for my classified ads module. This way I can have the category list look something like:

For Sale (225 Ads)
Musicians Looking (5 Ads)
etc.

Category Table:
category_id INT(11)
category_title VARCHAR(50)
category_description VARCHAR(255)

Ads Table:
ad_id INT(11)
ad_category_id INT(11) * This is the column that joins the 2 tables together.

I figured I would be able to do it with something like:

Code

SELECT c.*, COUNT(a.ad_id) AS adCount FROM {$jamroom_db['mcCategories'] c LEFT JOIN {$jamroom_db['mcAds']} a ON a.ad_category_id=c.category_id

But all that does is return the total count of ads within the ad table.

Any suggestions would be greatly appreciated,
-Tim

[SteveX]

I'm no expert but try a GROUP BY:

Code

SELECT c.*, COUNT(a.ad_id) AS adCount FROM {$jamroom_db['mcCategories'] c LEFT JOIN {$jamroom_db['mcAds']} a ON a.ad_category_id=c.category_id GROUP BY c.category_title ASC

_________________
Kulshi Mezian!

"Stranger from another planet, welcome to our hole. Just strap on your guitar and we'll play some rock and roll"

Ultrabubble create things.

[musiccreatures]

Brilliant! It works like a champ. Thank you so much SteveX. Your a gentleman and a scholar.

-Tim