From: | "Edward W(dot) Rouse" <erouse(at)comsquared(dot)com> |
---|---|
To: | <pgsql-sql(at)postgresql(dot)org> |
Subject: | Join question |
Date: | 2008-08-15 16:48:10 |
Message-ID: | 04b101c8fef6$b3c87d50$1b5977f0$@com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Edward W. Rouse | 2008-08-15 17:09:20 | Re: Join question |
Previous Message | Emi Lu | 2008-08-15 13:32:32 | Re: Check a column value not in Array. |