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.
