Re: Join question

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: pgsql-sql-owner(at)postgresql(dot)org [mailto:pgsql-sql-owner(at)postgresql(dot)org]
On Behalf Of Edward W. Rouse
Sent: Friday, August 15, 2008 12:48 PM
To: pgsql-sql(at)postgresql(dot)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