Skip site navigation (1) Skip section navigation (2)

Re: Join question

From: "Edward W(dot) Rouse" <erouse(at)comsquared(dot)com>
To: "'Daniel Hernandez'" <breydan(at)excite(dot)com>, <pgsql-sql(at)postgresql(dot)org>
Subject: Re: Join question
Date: 2008-08-19 13:35:52
Message-ID: 064001c90200$80eef7d0$82cce770$@com (view raw, whole thread or download thread mbox)
Lists: pgsql-sql
I have tried left, right outer and inner. 


Edward W. Rouse


From: Daniel Hernandez [mailto:breydan(at)excite(dot)com] 
Sent: Monday, August 18, 2008 12:30 PM
To: pgsql-sql(at)postgresql(dot)org; erouse(at)comsquared(dot)com
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(at)comsquared(dot)com]
Date: 08/15/2008 09:48 AM
To: pgsql-sql(at)postgresql(dot)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:


A    | emp1

B    | emp1

B    | emp2

B    | emp3

C    | emp2


Table b:


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 ( = and a.user = b.user)

where = ‘A’

group by, a.user

order by, a.user


I get:



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



Edward W. Rouse

In response to


pgsql-sql by date

Next:From: Edward W. RouseDate: 2008-08-19 13:36:39
Subject: Re: Join question
Previous:From: Yura GalDate: 2008-08-19 08:45:37
Subject: Re: Cursors..

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group