A
Adrienne
***
Sorry if this message came twice, I wanted to cross post, and sent it
before doing so. I tried to cancel the original.
***
I'm working with an Access DB, and two tables. This is going to be
ported to MS SQL shortly. The tables are category and merchantcategory,
and the link is on category.id and merchantcategory.category_id.
*** Category Table ***
id, category
1, apples
2, oranges
3, lemons
*** Merchantcategory Table ***
id, category_id, merchant_id
1, 1, 1
2, 3, 4
3, 3, 19
What I need to do is something like
SELECT category, count(merchant_id) AS merchants
FROM category, merchantcategory
WHERE category.id = merchantcategory.category_id
GROUP BY category
ORDER BY category
But, I need the merchants column to either return 0 or NULL for those
categories that do not have an associated merchant. I would like to see
something like:
1, 1
2, 0
3, 2
A. Is this possible?
B. If it is, what do I need to do to get the result? I would prefer not
to have a bunch of record sets and loops.
Sorry if this message came twice, I wanted to cross post, and sent it
before doing so. I tried to cancel the original.
***
I'm working with an Access DB, and two tables. This is going to be
ported to MS SQL shortly. The tables are category and merchantcategory,
and the link is on category.id and merchantcategory.category_id.
*** Category Table ***
id, category
1, apples
2, oranges
3, lemons
*** Merchantcategory Table ***
id, category_id, merchant_id
1, 1, 1
2, 3, 4
3, 3, 19
What I need to do is something like
SELECT category, count(merchant_id) AS merchants
FROM category, merchantcategory
WHERE category.id = merchantcategory.category_id
GROUP BY category
ORDER BY category
But, I need the merchants column to either return 0 or NULL for those
categories that do not have an associated merchant. I would like to see
something like:
1, 1
2, 0
3, 2
A. Is this possible?
B. If it is, what do I need to do to get the result? I would prefer not
to have a bunch of record sets and loops.