Right Join Question
I am trying to solve the following problem. I have a certain column,
to_state, that can take a finite set of values. I want to make sure that
my query always returns those sets of finite values, even if not all of
the values are represented in the results. In essence, I am looking to
expand the result returned by the query by using a right join.
When I do the following simple query, this seems to work.
create table hat (the_hat integer);
insert into hat values (1),(2),(3);
create table cat (the_hat integer);
insert into cat values (2),(3),(4),(5);
select the_hat from hat
right join cat c using(the_hat)
The right join changes the results query from 1,2,3 to 2,3,4,5.
However, when I use this on my actual query, I don't get any additional
rows by adding the right join.
select to_state from deal_trans_info
right join (select distinct to_state from deal_trans_info where deal =
'02FF1') foo using(to_state)
where deal = '02FF1' and
pool_id = 'C2V' and
coll_type = 'ARM 228' and
from_state = 'CBNA' and
"month" = '20090701'
I can't send the actual data since it is too big, but it seems like this
is analogous to the simple query above.
If anyone has any suggestions, that would be great.
Thanks,
Andrew
On Thu, Oct 22, 2009 at 12:43 PM, Bierbryer, Andrew
<abierbryer@klsdiversified.com> wrote:
I am trying to solve the following problem. I have a certain column,
to_state, that can take a finite set of values. I want to make sure that my
query always returns those sets of finite values, even if not all of the
values are represented in the results. In essence, I am looking to expand
the result returned by the query by using a right join.When I do the following simple query, this seems to work.
create table hat (the_hat integer);
insert into hat values (1),(2),(3);
create table cat (the_hat integer);
insert into cat values (2),(3),(4),(5);select the_hat from hat
right join cat c using(the_hat)The right join changes the results query from 1,2,3 to 2,3,4,5.
A right join will return all the rows on the right that aren't removed
by the where clause, and add the rows on the left that match, and
return nulls for those values where there is no match.
Perhaps you want a left join here?
Andrew,
I think you want a full outer join. If I understood correctly, you want all real data, plus all codes without data.
Doug
-----Original Message-----
From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Scott Marlowe
Sent: Thursday, October 22, 2009 6:07 PM
To: Bierbryer, Andrew
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Right Join Question
On Thu, Oct 22, 2009 at 12:43 PM, Bierbryer, Andrew
<abierbryer@klsdiversified.com> wrote:
I am trying to solve the following problem. I have a certain column,
to_state, that can take a finite set of values. I want to make sure that my
query always returns those sets of finite values, even if not all of the
values are represented in the results. In essence, I am looking to expand
the result returned by the query by using a right join.When I do the following simple query, this seems to work.
create table hat (the_hat integer);
insert into hat values (1),(2),(3);
create table cat (the_hat integer);
insert into cat values (2),(3),(4),(5);select the_hat from hat
right join cat c using(the_hat)The right join changes the results query from 1,2,3 to 2,3,4,5.
A right join will return all the rows on the right that aren't removed
by the where clause, and add the rows on the left that match, and
return nulls for those values where there is no match.
Perhaps you want a left join here?
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general