sql, counts, joins

[hometoast]

I have this query
SELECT categories.id as catid, categories.name as catname, segments.id AS sid, segments.name as sname FROM categories , segments WHERE (segments.id = categories.segment AND categories.segment=3;

to so me a list of subcategory names so I can modify them. (segments is a main category, and then categories are below that one level).

I have a 3rd table called ads, that has field category (related to categories.id).

I want to return a list of the category names, within the segment, and a count of how many ads contain that category. I'm totally lost. Could someone point me in the right direction?

Thahnk you
dan

[Anon]

when I do a JOIN, I always try to explicitly define on which condition the tables should be joined (i.e. "ON" or "USING") and on which condition the rows should be chosen ("WHERE" or "HAVING"). Thus, I would write:

SELECT categories.id AS catid, categories.name AS catname, segments.id AS sid, segments.name AS sname, COUNT(ads.id) AS adcount FROM categories LEFT JOIN segments ON (segments.id=categories.segment) LEFT JOIN ads ON (ads.category=category.id) WHERE (categories.segment=3) GROUP BY ads.category

I think that would work for what you are trying to accomplish.