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

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.