Re: Join question

----- Original Message -----

From: Daniel Hernandez

To: pgsql-sql@postgresql.org ; erouse@comsquared.com

Sent: Monday, August 18, 2008 5:30 PM

Subject: Re: [SQL] Join question

have you tried a right Join?


Daniel Hernndez.
San Diego, CA.
"The more you learn, the more you earn".
Fax: (808) 442-0427


-----Original Message-----
From: "Edward W. Rouse" [erouse@comsquared.com]
Date: 08/15/2008 09:48 AM
To: pgsql-sql@postgresql.org
Subject: Re: [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