Include Null Values in Result Set

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
2, 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.

--
Adrienne Boswell
http://www.cavalcade-of-coding.info
Please respond to the group so others can share

Re: Include Null Values in Result Set

Adrienne wrote:
> 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
> 2, 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?

Certainly

> B. If it is, what do I need to do to get the result?

Use an outer join:

SELECT category, count(merchant_id) AS merchants
FROM category c left join merchantcategory mc
ON c.id = mc.category_id
GROUP BY category
ORDER BY category

> I would prefer
> not to have a bunch of record sets and loops.

I don't blame you.

Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Re: Include Null Values in Result Set

Gazing into my crystal ball I observed "Bob Barrows [MVP]" @NOyahoo.SPAMcom> writing in news:#tuxwV1mFHA.4056@TK2MSFTNGP10.phx.gbl:

> Adrienne wrote:
>> 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
>> 2, 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?
>
> Certainly
>
>> B. If it is, what do I need to do to get the result?
>
> Use an outer join:
>
> SELECT category, count(merchant_id) AS merchants
> FROM category c left join merchantcategory mc
> ON c.id = mc.category_id
> GROUP BY category
> ORDER BY category
>
>> I would prefer
>> not to have a bunch of record sets and loops.
>
> I don't blame you.
>
> Bob Barrows

Bob, thanks so much for taking time for this. It works a treat!

--
Adrienne Boswell
http://www.cavalcade-of-coding.info
Please respond to the group so others can share