Re: Join question

--- On Tue, 8/19/08, Edward W. Rouse <erouse@comsquared.com> wrote:

> From: Edward W. Rouse <erouse@comsquared.com>
> Subject: Re: [SQL] Join question
> To: "'Daniel Hernandez'" <breydan@excite.com>, pgsql-sql@postgresql.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@excite.com]
> Sent: Monday, August 18, 2008 12:30 PM
> To: pgsql-sql@postgresql.org; erouse@comsquared.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@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

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