Re: Problem with left join when moving a column to another table

David,

Thank you very much for your response.
Below is a script that will reproduce the problem with comments
included.

/*******************************************************/

--drop table t_item;
--drop table t_price_base_table;
--drop table t_price_original_with_area_id;

--this table represents inventory line items
CREATE TABLE t_item
(
id bigint NOT NULL,
size_id bigint NOT NULL,
area_id bigint NOT NULL,
CONSTRAINT pk_t_item PRIMARY KEY (id)
);

INSERT INTO t_item VALUES (1, 1, 10);
INSERT INTO t_item VALUES (2, 4, 1);
INSERT INTO t_item VALUES (3, 19, 1);

-- I want to move the area_id(and other columns not listed here) to
another base table and left join it
CREATE TABLE t_price_original_with_area_id
(
id bigint NOT NULL,
size_id bigint NOT NULL,
area_id bigint NOT NULL,
CONSTRAINT pk_t_price_original_with_area_id PRIMARY KEY (id)
);

INSERT INTO t_price_original_with_area_id VALUES (162, 4, 6);
INSERT INTO t_price_original_with_area_id VALUES (161, 4, 2);
INSERT INTO t_price_original_with_area_id VALUES (159, 4, 1);
INSERT INTO t_price_original_with_area_id VALUES (638, 19, 9);
INSERT INTO t_price_original_with_area_id VALUES (633, 19, 14);
INSERT INTO t_price_original_with_area_id VALUES (675, 19, 45);
INSERT INTO t_price_original_with_area_id VALUES (64, 19, 1);

-- My simplified base table
CREATE TABLE t_price_base_table
(
id bigint NOT NULL,
area_id bigint NOT NULL,
CONSTRAINT pk_t_price_base_table PRIMARY KEY (id)
);

-- insert to add the information I want to transfer to the base table so
I can drop the area_id column
insert into t_price_base_table (id, area_id) (select id, area_id from
t_price_original_with_area_id);

/*
This is the working query. Note it joins size_id and area_id in one
left join.
It produces 1 row for each item. There is no match for item 1. Item 2
and 3
match the price table.
*/
select it.*,
pwoa.*
from t_item it
left join t_price_original_with_area_id pwoa on it.size_id=pwoa.size_id
and
it.area_id=pwoa.area_id
order by it.id;

/*
This is the new query that is not working correctly.
I am trying to left join the base table by its id and area_id.
I need a left join because there is no guarantee that there is a
matching price.

The where claues seems to work, but I the orginal query is much more
complicated,
and I will be needed to do a simiar join in may views.
*/
select it.*,
pwoa.*,
pbt.*
from t_item it
left join t_price_original_with_area_id pwoa on it.size_id=pwoa.size_id
left join t_price_base_table pbt on pbt.id=pwoa.id and
it.area_id=pbt.area_id
/*
where (pwoa.id is not null and pbt.id is not null) or
(pwoa.id is null and pbt.id is null)
*/
order by it.id;

/*******************************************************/

On Thu, 2013-06-20 at 12:29 -0700, David Johnston wrote:

> Jason Long-2 wrote
> > I am having some problems moving a column to another table and fixing
> > some views that rely on it. I want to move the area_id column from
> > t_offerprice_pipe to t_offerprice and then left join the results.
> >
> > When I have only one table I get the correct results. area_id is
> > currently in the t_offerprice_pipe. The working portion on the query is
> > below.
>
> Maybe someone else can make sense of your partial examples but I cannot.
> I'd suggest creating self-contained queries that exhibit both the correct
> and incorrect behavior. Use the following template:
>
> WITH from_table_not_specified (col1, col2) AS (
> VALUES (1, 1), (2, 2)
> )
> , t_offerprice_pipe (....) AS (
> VALUES (...), (....)
> )
> , to_offerprice (...) AS (
> VALUES (...), (...)
> )
> /* working query */
> SELECT *
> FROM from_table_not_specified
> LEFT JOIN t_offerprice_pipe op1 ON ...
> LEFT JOIN t_offerprice_pipe op2 ON ...
>
> /* not working query using same or similar CTEs where possible. */
> SELECT *
> FROM ...
> LEFT JOIN ...
> LEFT JOIN ...
> LEFT JOIN ...
> LEFT JOIN ...
>
> Without a working query it is really hard (impossible really) to debug
> "wrong number of rows" problems. Especially since the query itself is
> possibly not the problem but rather your data model is flawed.
>
> David J.
>
>
>
>
> --
> View this message in context: http://postgresql.1045698.n5.nabble.com/Problem-with-left-join-when-moving-a-column-to-another-table-tp5760187p5760192.html
> Sent from the PostgreSQL - general mailing list archive at Nabble.com.
>
>