I've got a query which is failing with an ambiguous column error. Unfortunately, because of the framework I'm using (Magento 1), I can't just resolve this problem by prefixing the column name with the correct table name in the 'where' clause.
Given the two example tables
T1 = colA | colB | colC
T2 = colD | colB | colE
SELECT colC, colE
FROM T1 LEFT JOIN T2 ON colA=colD
FROM T1 LEFT JOIN (SELECT colD,colE FROM T2) ON colA=colD
How does this perform?
SELECT colC, colE FROM T1 LEFT JOIN T2 ON colA = colD AND T1.colB = 1 WHERE T1.colB = 1;
Another alternative is a subquery that looks like this:
SELECT colC, (SELECT colE FROM T2 WHERE T1.colA = T2.colD ) as colE FROM T1 WHERE colB = 1;
This should perform as fast as the original query (which is presumably taking advantage of indexes.