I'd try to check why discounts are different. Join with 'or' should work. Build (one query) except all (another query) and check some rows from result.
3. I'm trying to eliminate the union, however I have two problems. A) I can't figure out how to have an 'or' clause in a single join that would fetch all the correct rows. If I just do: LEFT OUTER JOIN table2 t2 ON (t2.real_id = t.id OR t2.real_id = t.backup_id), I end up with many less rows than the original query. B.
I believe the issue with this is a row could have one of three possibilities: * part of the first query but not the second -> results in 1 row after the union * part of the second query but not the first -> results in 1 row after the union * part of the first query and the second -> results in 2 rows after the union (see 'B)' for why)
B) the third and fourth column in the SELECT will need to be different depending on what column the row is joined on in the LEFT OUTER JOIN to table2, so I may need some expensive case when logic to filter what is put there based on whether that row came from the first join clause, or the second.
No, it doesn't:
SELECT t.id, t.mycolumn1, table3.otherid as otherid1, table3a.otherid as otherid2, t.mycolumn2 FROM t LEFT OUTER JOIN table2 ON ( t.id = t2.real_id OR t.backup_id = t2.real_id ) LEFT OUTER JOIN table3 ON ( t.typeid = table3.id ) LEFT OUTER JOIN table3 as table3a ON ( table2.third_id = table3.id ) WHERE t.external_id IN ( ... ) ORDER BY t.mycolumn2, t.id
I tried this originally, however my resulting rowcount is different.
The original query returns 9,955,729 rows This above one returns 7,213,906
As for the counts on the tables: table1 3,653,472 table2 2,191,314 table3 25,676,589
I think it's safe to assume right now that any resulting joins are not one-to-one
