Urgent : SQL Query Problem

[RushiShroff]

Hellow all,
I am stuck here while making

http://www.offersforyou.com

(Check it out !)
DataBase : MS-Access 97
On the home page, I want to list out all main categories from Categories table.My classified offers lie in Classified table.
I have to list out all categories even if there are no offers for it.I mean if some category has no offer,in bracket it should show (0).

To achieve the count for all categoies, I create a query which has a relationship Cat_Code common in both the tables.Then I am counting the number of each category.So I know the total no of offers in that.

But this doesnt include those categories which are not there in classified table.So categories having no offers are not coming into picture.

Can anybody tell me how to get the count of all categories from
Classified table..??

Please,

Rushi Shroff

 

[derren]

Hi

Urgent request, poorly drafted reply!

select categories, count(categories) from categories LEFT JOIN classified ON categories.urn=classifies.urn GROUP BY categories

Haven't had time to test it as I am in a hurry as well

Good luck
Derren
[The only person in the world to like Word]

 

[RushiShroff]

Thanks ,
Now problem is it is showing count 1 for those categories also which doesnt have any offer(they are not in classified)

So how to seperate categories with actual offer 1 and no offer ??

Please !!

Rushi Shroff

 

[rohitbd]

Hi,

You could try this...

Let's say, A is the categories table ('cat_id' as PK)
B is the classifieds table ('class_id' as PK and 'cat_id' as FK to A)

select A.cat_id, count(B.class_id) from A, B where A.cat_id (+) = B.cat_id group by B.cat_id, B.class_id order by B.class_id

Hope this helps...