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.
