Join question

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

Responses

Browse pgsql-sql by date

  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.