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

Re: Join question

From: "Edward W(dot) Rouse" <erouse(at)comsquared(dot)com>
To: <pgsql-sql(at)postgresql(dot)org>
Subject: Re: Join question
Date: 2008-08-15 17:09:20
Message-ID: 04c601c8fef9$a8a29c00$f9e7d400$@com (view raw or flat)
Thread:
Lists: pgsql-sql
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

In response to

pgsql-sql by date

Next:From: Richard BroersmaDate: 2008-08-15 17:10:27
Subject: Re: Join question
Previous:From: Edward W. RouseDate: 2008-08-15 16:48:10
Subject: Join question

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