Re: Problem with left join when moving a column to another table - Mailing list pgsql-general

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.