Thread: Join question

Join question

From

"Edward W. Rouse"

Date:

15 August 2008, 16:57:15

<div class="Section1"><p class="MsoNormal">I have 2 tables, both have a user column. I am currently using a left join
fromtable a to table b because I need to show all users from table a even those not having an entry in table b. The
problemis I also have to include items from table b with that have a null user. There are some other criteria as well
thatare simple where clause filters. So as an example:<p class="MsoNormal"> <p class="MsoNormal">Table a:<p
class="MsoNormal">Org|user<pclass="MsoNormal">A    | emp1<p class="MsoNormal">B    | emp1<p class="MsoNormal">B    |
emp2<pclass="MsoNormal">B    | emp3<p class="MsoNormal">C    | emp2<p class="MsoNormal"> <p class="MsoNormal">Table
b:<pclass="MsoNormal">Org|user|color<p class="MsoNormal">A   |emp1|red<p class="MsoNormal">A   |emp1|blue<p
class="MsoNormal">A  |null|pink<p class="MsoNormal">A   |null|orange<p class="MsoNormal">B   |emp1|red<p
class="MsoNormal">B  |emp3|red<p class="MsoNormal">B   |null|silver<p class="MsoNormal">C   |emp2|avacado<p
class="MsoNormal"> <pclass="MsoNormal">If I:<p class="MsoNormal"> <p class="MsoNormal">select org, user, count(total)<p
class="MsoNormal">froma left join b<p class="MsoNormal">on (a.org = b.org and a.user = b.user)<p
class="MsoNormal">wherea.org = ‘A’<p class="MsoNormal">group by a.org, a.user<p class="MsoNormal">order by a.org,
a.user<pclass="MsoNormal"> <p class="MsoNormal">I get:<p class="MsoNormal"> <p class="MsoNormal">Org|user|count<p
class="MsoNormal">A   |emp1|2<p class="MsoNormal">A    |emp2|0<p class="MsoNormal">A    |emp3|0<p class="MsoNormal"> <p
class="MsoNormal">Butwhat I need is:<p class="MsoNormal"> <p class="MsoNormal">A    |emp1|2<p class="MsoNormal">A
   |emp2|0<pclass="MsoNormal">A    |emp3|0<p class="MsoNormal">A    |null|2<p class="MsoNormal"> <p
class="MsoNormal">Thanks,<pclass="MsoNormal">Edward W. Rouse</div>

Re: Join question

From

"Edward W. Rouse"

Date:

15 August 2008, 17:09:29

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@postgresql.org [mailto:pgsql-sql-owner@postgresql.org] On Behalf Of Edward W. Rouse
Sent: Friday, August 15, 2008 12:48 PM
To: pgsql-sql@postgresql.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

Re: Join question

From

"Richard Broersma"

Date:

15 August 2008, 17:10:43

On Fri, Aug 15, 2008 at 9:48 AM, Edward W. Rouse <erouse@comsquared.com> wrote:

> 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:

instead of left join try FULL OUTER JOIN.

-- 
Regards,
Richard Broersma Jr.

Visit the Los Angeles PostgreSQL Users Group (LAPUG)
http://pugs.postgresql.org/lapug

Re: Join question

From

"Edward W. Rouse"

Date:

15 August 2008, 17:46:48

I did try that, but I can't get both the values from table a with no entries
in table b and the values from table b with null entries to show up. It's
either one or the other.

Edward W. Rouse


-----Original Message-----
From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org]
On Behalf Of Richard Broersma
Sent: Friday, August 15, 2008 1:10 PM
To: Edward W. Rouse
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] Join question

On Fri, Aug 15, 2008 at 9:48 AM, Edward W. Rouse <erouse@comsquared.com>
wrote:

> 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:

instead of left join try FULL OUTER JOIN.

-- 
Regards,
Richard Broersma Jr.

Visit the Los Angeles PostgreSQL Users Group (LAPUG)
http://pugs.postgresql.org/lapug

-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: Join question

From

Steve Midgley

Date:

16 August 2008, 03:28:50

At 12:20 PM 8/15/2008, pgsql-sql-owner@postgresql.org wrote:
>Date: Fri, 15 Aug 2008 13:46:14 -0400
>From: "Edward W. Rouse" <erouse@comsquared.com>
>To: <pgsql-sql@postgresql.org>
>Subject: Re: Join question
>Message-ID: <04da01c8fefe$d01f9c60$705ed520$@com>
>
>I did try that, but I can't get both the values from table a with no 
>entries
>in table b and the values from table b with null entries to show up. 
>It's
>either one or the other.
>
>Edward W. Rouse

Might have luck with applying some additional WHERE clause criteria to 
your full outer join. So if you don't want certain types NULL's in 
table b, restrict against that in WHERE clause? I could be 
misunderstanding the whole thing though..

Steve

Re: Join question

From

"Daniel Hernandez"

Date:

18 August 2008, 16:59:56

<font style="{font-family: Arial,Verdana, Sans-Serif;font-size: 10pt;}"> have you tried a right Join?<br /><br /><br />
DanielHernndez.<br /> San Diego, CA.<br /> "The more you learn, the more you earn".<br /> Fax: (808) 442-0427<br /><br
/><br/> -----Original Message-----<br /><b>From: </b>"Edward W. Rouse" [erouse@comsquared.com]<br /><b>Date:
</b>08/15/200809:48 AM<br /><b>To: </b>pgsql-sql@postgresql.org<br /><b>Subject: </b>Re: [SQL] Join question<br /><br
/><divclass="Section1"><p class="MbtfsoNormal">I have 2 tables, both have a user column. I am currently using a left
joinfrom table a to table b because I need to show all users from table a even those not having an entry in table b.
Theproblem is I also have to include items from table b with that have a null user. There are some other criteria as
wellthat are simple where clause filters. So as an example:<p class="MbtfsoNormal"> <p class="MbtfsoNormal">Table a:<p
class="MbtfsoNormal">Org|user<pclass="MbtfsoNormal">A    | emp1<p class="MbtfsoNormal">B    | emp1<p
class="MbtfsoNormal">B   | emp2<p class="MbtfsoNormal">B    | emp3<p class="MbtfsoNormal">C    | emp2<p
class="MbtfsoNormal"> <pclass="MbtfsoNormal">Table b:<p class="MbtfsoNormal">Org|user|color<p class="MbtfsoNormal">A  
|emp1|red<pclass="MbtfsoNormal">A   |emp1|blue<p class="MbtfsoNormal">A   |null|pink<p class="MbtfsoNormal">A  
|null|orange<pclass="MbtfsoNormal">B   |emp1|red<p class="MbtfsoNormal">B   |emp3|red<p class="MbtfsoNormal">B  
|null|silver<pclass="MbtfsoNormal">C   |emp2|avacado<p class="MbtfsoNormal"> <p class="MbtfsoNormal">If I:<p
class="MbtfsoNormal"> <pclass="MbtfsoNormal">select org, user, count(total)<p class="MbtfsoNormal">from a left join b<p
class="MbtfsoNormal">on(a.org = b.org and a.user = b.user)<p class="MbtfsoNormal">where a.org = ‘A’<p
class="MbtfsoNormal">groupby a.org, a.user<p class="MbtfsoNormal">order by a.org, a.user<p class="MbtfsoNormal"> <p
class="MbtfsoNormal">Iget:<p class="MbtfsoNormal"> <p class="MbtfsoNormal">Org|user|count<p class="MbtfsoNormal">A   
|emp1|2<pclass="MbtfsoNormal">A    |emp2|0<p class="MbtfsoNormal">A    |emp3|0<p class="MbtfsoNormal"> <p
class="MbtfsoNormal">Butwhat I need is:<p class="MbtfsoNormal"> <p class="MbtfsoNormal">A    |emp1|2<p
class="MbtfsoNormal">A   |emp2|0<p class="MbtfsoNormal">A    |emp3|0<p class="MbtfsoNormal">A    |null|2<p
class="MbtfsoNormal"> <pclass="MbtfsoNormal">Thanks,<p class="MbtfsoNormal">Edward W. Rouse</div></font>

Re: Join question

From

"Oliveiros Cristina"

Date:

18 August 2008, 18:00:30

I don't understand your count(total) expression...

It doesnt work, because apparently you dont have any "total" column...

Apparently, you meant count(color)

The problem is that you are grouping by a.org,a.user and on table "a" u actually dont have any "null" users...

Well, if it is to include "null" users, a quick and dirty solution I can think of would be to add a "dummy" null user to every diferent org on table a and then

substitute your LEFT OUTER JOIN condition by this one :

from a left join b

on (a.org = b.org and (a.user = b.user OR (a.user is null and b.user is null )))

Now, I don' know if "null" users on table "a" will violate any constraints you may have (e.g. NOT NULL) ...

I know This is not a very elegant solution, but seems to give the results you need....

Best,

Oliveiros

----- 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

Re: Join question

From

"Edward W. Rouse"

Date:

19 August 2008, 13:37:11

I thought of that, but it does violate table constraints.

Edward W. Rouse

From: Oliveiros Cristina [mailto:oliveiros.cristina@marktest.pt]
Sent: Monday, August 18, 2008 2:00 PM
To: pgsql-sql@postgresql.org; erouse@comsquared.com
Subject: Re: [SQL] Join question

I don't understand your count(total) expression...

It doesnt work, because apparently you dont have any "total" column...

Apparently, you meant count(color)

The problem is that you are grouping by a.org,a.user and on table "a" u actually dont have any "null" users...

Well, if it is to include "null" users, a quick and dirty solution I can think of would be to add a "dummy" null user to every diferent org on table a and then

substitute your LEFT OUTER JOIN condition by this one :

from a left join b

on (a.org = b.org and (a.user = b.user OR (a.user is null and b.user is null )))

Now, I don' know if "null" users on table "a" will violate any constraints you may have (e.g. NOT NULL) ...

I know This is not a very elegant solution, but seems to give the results you need....

Best,

Oliveiros

----- 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

Re: Join question

From

"Edward W. Rouse"

Date:

19 August 2008, 13:37:16

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

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

Re: Join question

From

"Oliveiros Cristina"

Date:

19 August 2008, 15:18:25

Already tried making two queries and then outputting the UNION of the results?

The second one could be something like this...

SELECT org,null,COUNT(color)
FROM b
WHERE user IS NULL

AND org = 'a'
GROUP BY org

Best,

Oliveiros

----- Original Message -----
From: Edward W. Rouse
To: pgsql-sql@postgresql.org
Sent: Tuesday, August 19, 2008 2:36 PM
Subject: Re: [SQL] Join question

I thought of that, but it does violate table constraints.

Edward W. Rouse

From: Oliveiros Cristina [mailto:oliveiros.cristina@marktest.pt]
Sent: Monday, August 18, 2008 2:00 PM
To: pgsql-sql@postgresql.org; erouse@comsquared.com
Subject: Re: [SQL] Join question

I don't understand your count(total) expression...
It doesnt work, because apparently you dont have any "total" column...
Apparently, you meant count(color)

The problem is that you are grouping by a.org,a.user and on table "a" u actually dont have any "null" users...

Well, if it is to include "null" users, a quick and dirty solution I can think of would be to add a "dummy" null user to every diferent org on table a and then
substitute your LEFT OUTER JOIN condition by this one :

from a left join b

on (a.org = b.org and (a.user = b.user OR (a.user is null and b.user is null )))

Now, I don' know if "null" users on table "a" will violate any constraints you may have (e.g. NOT NULL) ...

I know This is not a very elegant solution, but seems to give the results you need....

Best,
Oliveiros
----- 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

Re: Join question

From

Lennin Caro

Date:

19 August 2008, 16:00:14



--- 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

Re: Join question

From

"Edward W. Rouse"

Date:

19 August 2008, 18:04:51

I was trying to do something like this, but couldn't get it to work. I am trying to follow the example you provided,
butdon't understand how id and oid relate to the example tables and which table is pr1 and pr2. Also my data has to
match2 constraints, not 1 (though I'm guessing that I could just add the other without changing anything else). And you
havepr2.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@postgresql.org [mailto:pgsql-sql-owner@postgresql.org] On Behalf Of Lennin Caro
Sent: Tuesday, August 19, 2008 11:59 AM
To: 'Daniel Hernandez'; pgsql-sql@postgresql.org; Edward W. Rouse
Subject: Re: [SQL] 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





--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: Join question

From

"Edward W. Rouse"

Date:

19 August 2008, 21:16:27

Finally got it to work. I used 2 separate selects and a union. So one of the selects was like my original left outer
joinedselect and then I unioned it with one that got the missed nulls from the other table. 

Thanks for all the advice.

Edward W. Rouse


-----Original Message-----
From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org] On Behalf Of Edward W. Rouse
Sent: Tuesday, August 19, 2008 2:04 PM
To: pgsql-sql@postgresql.org
Subject: Re: [SQL] 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,
butdon't understand how id and oid relate to the example tables and which table is pr1 and pr2. Also my data has to
match2 constraints, not 1 (though I'm guessing that I could just add the other without changing anything else). And you
havepr2.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@postgresql.org [mailto:pgsql-sql-owner@postgresql.org] On Behalf Of Lennin Caro
Sent: Tuesday, August 19, 2008 11:59 AM
To: 'Daniel Hernandez'; pgsql-sql@postgresql.org; Edward W. Rouse
Subject: Re: [SQL] 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





--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql


--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql