counting rows in another table

Here's what I'm trying to do:

I have an ITEMS table:
item_id integer not null primary key

name       varchar(100)
cat_id     integer references categories(cat_id)

And naturally a CATEGORIES table:
cat_id     integer not null primary key
name       varchar(100)

Every item belongs in a category, but every category doesn't necessarily have any items in it (yet).

What I'd like to do is SELECT out a row from the CATEGORIES table based on the cat_id (easy enough -- it's the primary key), but I'd also like to know how many rows in the ITEMS table are in this category.

My first inclination was:
SELECT cat_id, name,

       count(i.item_id) as item_count
FROM items i, categories c
WHERE c.cat_id = 7
AND c.cat_id = i.cat_id

This gives the desired result, with one exception: if there are no items in this category, the second clause (c.cat_id = i.cat_id) fails and returns back NO rows.

How might I return back the correct value (i.e. one row with item_count=0) with a single SQL statement?

I've tried various ways, including a SUM(DECODE(...,1,0)) which I never could get to work.

Any thoughts?

Thanks,

--Jeff

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Mon Mar 06 2000 - 17:46:03 CST