Hmmm... I'm using 7.1.2, and don't know if you should upgrade or not. Can you use UNIONs?
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 client_id from sales) order by 2; Yes, when I said "Try a left join" I should have said "left outer join"...the "OUTER" is commonly omitted. Thanks. :) Note that with UNION, you refer to columns by number, hence "order by 2". David On 9/27/01 11:35 AM, "Janning Vygen" <[EMAIL PROTECTED]> wrote: > Am Donnerstag, 27. September 2001 17:18 schrieb David Christian: >> Try a left join: >> >> select name, count(client_id) AS cnt from clients left join sales >> on (client_id = id) group by name order by cnt; > > # select name, count(client_id) AS cnt from clients left join sales on > (client_id = id) group by name order by cnt; > > ERROR: OUTER JOIN is not yet supported > (PostgreSQL) 7.0.3 > > do i need to upgrade or is there a workaround?? > >> There's a great book for SQL called "The Practical SQL Handbook" >> which covers these types of problems well. Published by Addison >> Wesley, written by Bowman, Emerson, and Darnovsky. Good bedtime >> reading. :) > > can you read my mind?? I am looking for a really good book on SQL. i > will buy this and we'll see how tired i will be tomorrow.. > > thanks > janning > >> HTH, >> David >> >> On 9/27/01 10:51 AM, "Janning Vygen" <[EMAIL PROTECTED]> wrote: >>> I need some help please, >>> >>> i am having two simple tables: >>> >>> # select * from sales; >>> client_id | product >>> -----------+--------- >>> 1 | toolbox >>> 1 | nails >>> 2 | nuts >>> >>> # select * from clients; >>> id | name >>> ----+------- >>> 1 | peter >>> 2 | john >>> 3 | marc >>> >>> now i want to show all client name and the count of the sales in >>> one table like this: >>> >>> # select name, count(sales.client_id) from clients, sales where >>> sales.client_id = clients.id group by name; >>> >>> name | count >>> -------+------- >>> john | 1 >>> peter | 2 >>> >>> works fine, but where is marc??? it should look like >>> >>> name | count >>> -------+------- >>> john | 1 >>> peter | 2 >>> marc | 0 >>> >>> who can i make it work??? i think i know why my select statement >>> doesnt work, because of the where clause marc will never join >>> this table because the condition will never be true... but how >>> can i do it?? >>> >>> i guess its very very simple, but i just cant manage it. >>> >>> thanks in advance >>> janning >>> >>> ---------------------------(end of >>> broadcast)--------------------------- TIP 3: if posting/reading >>> through Usenet, please send an appropriate subscribe-nomail >>> command to [EMAIL PROTECTED] so that your message can get >>> through to the mailing list cleanly ---------------------------(end of broadcast)--------------------------- TIP 6: Have you searched our list archives? http://archives.postgresql.org
