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. > >
