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
