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

[Ruslan Zasukhin]

on 12/3/03 4:45 PM, Aaron Bratcher at aaronbratcher at abdatatools.com wrote:

> 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?

Hi Aaron,

It seems right now Valentina  cannot do this.

We with Ivan have see in MS SQL very good idea for this.
Sybase do this in wrong way on our opinion.

So we will try future make it as in MS SQL.
Even better, for 2.0 we add more functions that can work with related
records. This should be even faster then join + group by.


-- 
Best regards,
Ruslan Zasukhin      [ I feel the need...the need for speed ]
-------------------------------------------------------------
e-mail: ruslan at paradigmasoft.com
web: http://www.paradigmasoft.com

To subscribe to the Valentina mail list go to:
http://lists.macserve.net/mailman/listinfo/valentina
-------------------------------------------------------------