Hi, I'm new to php/mysql, and I'm currently working with 2 databases and I need to get some info out of both of them using a join statement, but I'm having some trouble.

The first table is a category table (for future reference, I'll call it CAT). It holds cat_id, cat_name, and cat_description.

The second table is a listings table (I'll call this one LIST). It holds data about the listings, each of which has a cat_id to link it to a specific category.

What I want to do output a list of each category name from CAT and then the number of total listings in the LIST table with a matching cat_id.

So it could look like this:

Books (3)
Carrots (5)
Teddy Bears (43)
T-Shirts (9)
Fish (0)

The query that I have written right now works fine ONLY IF there is at least one listing in the LIST table with a cat_id that matches the cat_id of a category from the CAT table. So if I wanted to add a category, it wouldn't show up in the list above until there is a listing in the LIST table that has a matching cat_id (in otherwords, a listing that is in that particular category).

This is my query currently (xPsuCategories is CAT and xPsuClassifieds is LIST)

SELECT cat.cat_name, COUNT( list.cat_id )
FROM xPsuCategories cat, xPsuClassifieds list
WHERE list.cat_id= cat.cat_id
GROUP BY cat.cat_name;

The WHERE clause is the problem. This query would not display the Fish category from the list above because there is no listing in the LIST table that has a cat_id that equals a cat_id from the CAT table.

Ideas?

Cheers,
Cam

Try this..


SELECT cat.cat_name, COUNT( list.cat_id )
FROM xPsuCategories cat LEFT JOIN xPsuClassifieds list
ON list.cat_id= cat.cat_id
GROUP BY cat.cat_name;


Should do what you want..