Search Postgresql Archives

Problem with left join when moving a column to another table

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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.

I am joining the price.t_offerprice_pipe twice because I am looking for a wild card with manufacturer_id=-100 that has lower precedence than a specific manufacturer_id

LEFT JOIN t_offerprice_pipe opp ON opp.size_id = st.size_id AND
                                                             opp.manufacturer_id = st.manufacturer_id AND
                                                             opp.area_id = c.area_id
LEFT JOIN price.t_offerprice_pipe opam ON opam.size_id = st.size_id AND
                                                                        opam.manufacturer_id = (-100) AND
                                                                        opam.area_id = c.area_id


After moving the column to t_offerprice I am attempting to add a second left join, but is not working as I expected.  I am getting multiple results from this query.

LEFT JOIN t_offerprice_pipe opp ON opp.size_id = st.size_id AND
                                                             opp.manufacturer_id = st.manufacturer_id
LEFT JOIN t_offerprice op ON op.id = opp.id AND   
                                                  op.area_id = c.area_id
LEFT JOIN price.t_offerprice_pipe oppam ON oppam.size_id = st.size_id AND
                                                                          oppam.manufacturer_id = (-100)
LEFT JOIN t_offerprice opam ON opam.id = oppam.id AND   
                                                      opam.area_id = c.area_id

This is a stripped down version of the query for clarity.

I tried moving the condition into the where clause with no success.

I would greatly appreciate any advice on rewriting this query.
                                                       

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux