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

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