Am Donnerstag, 27. September 2001 19:01 schrieb David Christian:
> > btw: intersting point is, that the solution with union doesnt
> > work when client_id might be null.
> >
> > dont knwo why ...
>
> In this case, use "select all". Though, I advise against null
> fields in this table. Something like this would be better:
>
> create table sales (client_id integer default 0 not null, product
> text);
>
> That way you always have a "known" value. The value 0 (or whatever
> else you choose) means there is no client id associated with the
> product (or sale or whatever it is you are doing).
ok this is a good hint!
> Anyway, here's the statement that will work in your case:
>
> select name, count(client_id) from sales, clients where client_id =
> id group by name UNION select name,0 from clients where not id in
> (select all client_id from sales) order by 2;
'ALL' is the default, so it doesn't change anything.
it still dont work with NULL values.
here are my tables with a NULL value in the client_id field.
# select * from sales;
client_id | product
-----------+----------
1 | toolbox
1 | nails
2 | nuts
| junkfood
# select client_id from sales;
client_id
-----------
1
1
2
# select * from clients;
id | name
----+-------
1 | peter
2 | john
3 | marc
# select name,0 from clients where not id in (select all client_id
from sales) order by 2;
name | ?column?
------+----------
(0 rows)
THE FOLLOWING STATEMENT WORKS! WHY????
#select name,0 from clients where not id in (select all client_id
from sales where client_id is not null) order by 2;
name | ?column?
------+----------
marc | 0
(1 row)
Whats the difference?? when i ask for rows with "not id in" and my id
is 3 and the subquery returns a NULL but no '3' than '3 is in NULL'
???
Look at this:
select 'funny' where not 3 in (NULL);
?column?
----------
(0 rows)
3 is not in NULL!!
testdb=# select 'funny' where not 3 in ( 2, 1);
?column?
----------
funny
(1 row)
Janning