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 -- Planwerk 6 /websolutions Herzogstraße 86 40215 Düsseldorf fon 0211-6015919 fax 0211-6015917 http://www.planwerk6.de ---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
