Sigh, I messed up the tables a bit when I typed the example, org A was supposed to have entries for all 3 users in table a just like org B does, not just the one. Sorry for the confusion.
Edward W. Rouse From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Edward W. Rouse Sent: Friday, August 15, 2008 12:48 PM To: pgsql-sql@postgresql.org Subject: [SQL] Join question I have 2 tables, both have a user column. I am currently using a left join from table a to table b because I need to show all users from table a even those not having an entry in table b. The problem is I also have to include items from table b with that have a null user. There are some other criteria as well that are simple where clause filters. So as an example: Table a: Org|user A | emp1 B | emp1 B | emp2 B | emp3 C | emp2 Table b: Org|user|color A |emp1|red A |emp1|blue A |null|pink A |null|orange B |emp1|red B |emp3|red B |null|silver C |emp2|avacado If I: select org, user, count(total) from a left join b on (a.org = b.org and a.user = b.user) where a.org = 'A' group by a.org, a.user order by a.org, a.user I get: Org|user|count A |emp1|2 A |emp2|0 A |emp3|0 But what I need is: A |emp1|2 A |emp2|0 A |emp3|0 A |null|2 Thanks, Edward W. Rouse
