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.
From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org] 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
