Revising a query: give a noob a hand

pkghost

there are two tables involved. What I am aiming for is a list of all the categories from the table Categories with a count of the number of listings from the table Listings that have a matching cat_id (category id). So it could look like this:

Books - 23
Computers - 12
Cars - 37
Bunnies- 94
Opium Dens - 0

where books, computers, cars, bunnies, and opium dens are all categories from the Category table and the numbers following each category are totals of the rows from the Listings table whose cat_id matches the cat_id of a row from the Categories table.

this is the query i have written as of now. unfortunately, this doesn't return any categories that have zero matches (see the WHERE clause).

SELECT list.cat_id, cat.cat_id, cat.cat_name, COUNT( list.cat_id )
FROM Listings list, Categories cat
WHERE list.cat_id = cat.cat_id
GROUP BY list.cat_id"

can anyone help me out?

cheese

Hi PK,

Had you thought about putting the catagory_id in the same table. Doing that you could do it the same was as the example shown in Writing a join'ed MySQL Query
If you are entering the book details on a form, then you could get this data from your catagory_id in the other table (create a combo box via a MySQL query) and it would allow you to join the tables with common data at a later stage IF you needed to.

Hope this help,

Neil.

xblue

Hi cheese,

it doesn't seem really appropriate to me to store all that data in 1 table, seems like a classical 1:n relation, or do I get something wrong?

I'd think a left join may help
http://dev.mysql.com/doc/mysql/en/join.html

pkghost

The reason that I have two separate tables is because there will be a point in time when I have categories that have no listings in them. Originally, I had cat_name as a column in the Listings table, however this prevented me from adding categories unless I added a bogus listing with the desired category name. Also, keeping a separate table for categories allows me to store a description of each category. right now the tables are set up like this:

Categories:
cat_id ; cat_name ; cat_description

Listings:

id ; cat_id ; title ; description

1 | 1 | Moby Dick | 1st edition, signed |
2 | 4 | Curious George | neutered, no longer curious |

i hope thats enough to convey my meaning

edit it didn't format quite as i expected, sorry if its confusing..