outer join and count(*) SQL question...

[Aaron Bratcher]

Can I do the following:

I have 2 tables, the second is a list lookup table.

I want to get a count of all items in the 2nd table. If the item isn't 
used, then to have a zero.

I tried this SQL command:

select category,lists.listValue,count(*) from roster1,lists where 
roster1.race =* lists.listID and lists.listName = 'race' group by 
lists.listValue

I was expecting something like this to be returned:

Race	Asian/Pacific Islander	0
Race	Black	0
Race	Hispanic	0
Race	Native American/Alaskan	0
Race	Other	0
Race	White	4


but instead got this:

Race	Asian/Pacific Islander	1
Race	Black	1
Race	Hispanic	1
Race	Native American/Alaskan	1
Race	Other	1
Race	White	4

Which makes sense I guess because 1 record is now being returned for 
all of the non-used races.  Is there a way with SQL to get what I want? 
Or will I have to do this in small steps, appending my answers to a 
table?

Thanks for any input.


--
Aaron Bratcher
ab DataTools
http://www.abDataTools.com