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

On Thu, 2013-06-20 at 15:37 -0700, David Johnston wrote:

Jason Long-2 wrote
> David,
> 
> Thank you very much for your response.
> Below is a script that will reproduce the problem with comments
> included.
> 
> ....
> 
> /*
> 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 query I am pretty sure you want is:

WITH item (i_id, size_id, area_id) AS (
VALUES (1,1,10),(2,4,1),(3,19,1)
)
, price_orig (p_id, size_id, area_id) AS (
VALUES
(162,4,6),(161,4,2),(159,4,1),(638,19,9),(633,19,14),(675,19,45),(64,19,1)
)
, simple_base (p_id, area_id) AS ( SELECT p_id, area_id FROm price_orig
)
--SELECT * FROM item LEFT JOIN price_orig USING (size_id, area_id)
--original
/*  your problem query
SELECT * FROM item 
LEFT JOIN price_orig USING (size_id)
LEFT JOIN simple_base ON (price_orig.p_id = simple_base.p_id AND
item.area_id = simple_base.area_id)
*/

-- the correct query
SELECT * FROM item 
LEFT JOIN (SELECT p_id, price_orig.size_id, simple_base.area_id FROM
price_orig JOIN simple_base USING (p_id)) rebuild    USING (size_id, area_id)

In the original query you used both size and area to link to the price
table.  Even though you have moved the area to a different table in order to
keep the same semantics you have to continue performing the same relational
join.  If you intend something different then you are not providing enough
information since neither size_id nor area_id are unique within the price
table.  Because the combination of the two just happens to not be duplicated
in the supplied data the correct queries only return a single result per
item.

There is a unique constraint on the real price table.  I hadn't thought of how I will enforce the constraint across two tables.
size_id and area_id will have to be unique across both t_price_base_table and t_price_original_with_area_id.  I will want to drop area_id from t_price_original_with_area_id.

What is the best way to implement the cross table unique constraint?

In the "correct" query I am providing I am first re-joining (with an inner
join) the two tables so that they appear just like the original table
appeared.  Then I am joining the "view" to the items table using both size
and area.

The fundamental problem is that you really do not want right-hand tables in
left joins to refer to each other.

FROM item 
LEFT JOIN price_orig ON item = price_orig
LEFT JOIN price_base ON item = price_base    AND price_orig = price_base --<
the second AND expression is the problem.

I do not even try to remember nesting rules for JOIN generally.  My basic
form is:

FROM
INNER*
LEFT* (with the ON clause only referring to tables joined via INNER)

if my solution requires a different usage I either move parts of the query
into CTEs or I start explicitly adding parenthesis to explicitly group the
different pieces - and adding INNER JOIN where necessary like I did for your
example.

David J.





--
View this message in context: http://postgresql.1045698.n5.nabble.com/Problem-with-left-join-when-moving-a-column-to-another-table-tp5760187p5760210.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.