Re: Join question

I was trying to do something like this, but couldn't get it to work. I am trying to follow the example you provided, but don't understand how id and oid relate to the example tables and which table is pr1 and pr2. Also my data has to match 2 constraints, not 1 (though I'm guessing that I could just add the other without changing anything else). And you have pr2.dato in the inner select but not the outer one. Is there a reason for that.

As of now I am thinking I will have to break this up into more than one statement.

Edward W. Rouse

-----Original Message-----
From: pgsql-sql-owner(at)postgresql(dot)org [mailto:pgsql-sql-owner(at)postgresql(dot)org] On Behalf Of Lennin Caro
Sent: Tuesday, August 19, 2008 11:59 AM
To: 'Daniel Hernandez'; pgsql-sql(at)postgresql(dot)org; Edward W. Rouse
Subject: Re: [SQL] Join question

--- On Tue, 8/19/08, Edward W. Rouse <erouse(at)comsquared(dot)com> wrote:

> From: Edward W. Rouse <erouse(at)comsquared(dot)com>
> Subject: Re: [SQL] Join question
> To: "'Daniel Hernandez'" <breydan(at)excite(dot)com>, pgsql-sql(at)postgresql(dot)org
> Date: Tuesday, August 19, 2008, 1:35 PM
> 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:
>
> 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

also like this...

select id1,dato1, count(id2) from
(
select pr1.id as id1,pr1.dato as dato1,pr2.oid as id2,pr2.dato from pr1 right outer join pr2 on (pr1.id = pr2.oid)
) a group by id1,dato1