[5.0] Left Join Problem
I'm trying to upgrade from MySQL 4.1 to MySQL 5.0. A query that works in MySQL 4.1 does not work in MySQL 5.0 and I'm at a loss as to how to proceed. SELECT tab_a.item1, tab_b.item2, tab_c.item3, tab_d.item4, tab_e.item5, tab_f.item6 FROM db.tab_a, db.tab_e, db.tab_f LEFT JOIN db.tab_b ON tab_b.id = tab_a.id LEFT JOIN db.tab_c ON tab_c.id = tab_a.id LEFT JOIN db.tab_d ON tab_d.id = tab_a.id WHERE tab_a.id = 'value' AND tab_a.id2 = tab_e.id2 AND tab_e.id3 = tab_f.id3 LIMIT 1; The above query now generates this error: Unknown column 'tab_a.id in 'on clause'. Placing parentheses around the ON clause, LEFT JOIN db.tab_b ON (tab_b.id = tab_a.id) does not work. However, splitting the original query apart and grouping the Left Joins in one query and the regular joins in another query does NOT generates any errors: SELECT tab_a.item1, tab_b.item2, tab_c.item3, tab_d.item4 FROM db.tab_a LEFT JOIN db.tab_b ON tab_b.id = tab_a.id LEFT JOIN db.tab_c ON tab_c.id = tab_a.id LEFT JOIN db.tab_d ON tab_d.id = tab_a.id WHERE tab_a.id = 'value' LIMIT 1; - this is ok AND SELECT tab_a.item1, tab_e.item5, tab_f.item6 FROM db.tab_a, db.tab_e, db.tab_f WHERE tab_a.id = 'value' AND tab_a.id2 = tab_e.id2 AND tab_e.id3 = tab_f.id3 LIMIT 1; - this is ok Why doesn't the original query work in MySQL 5.0? What do I need to do to make it work? TIA Stephen P. Fracek, Jr. [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [5.0] Left Join Problem
At 12:47 PM -0500 7/21/06, Stephen P. Fracek, Jr. wrote: I'm trying to upgrade from MySQL 4.1 to MySQL 5.0. A query that works in MySQL 4.1 does not work in MySQL 5.0 and I'm at a loss as to how to proceed. SELECT tab_a.item1, tab_b.item2, tab_c.item3, tab_d.item4, tab_e.item5, tab_f.item6 FROM db.tab_a, db.tab_e, db.tab_f LEFT JOIN db.tab_b ON tab_b.id = tab_a.id LEFT JOIN db.tab_c ON tab_c.id = tab_a.id LEFT JOIN db.tab_d ON tab_d.id = tab_a.id WHERE tab_a.id = 'value' AND tab_a.id2 = tab_e.id2 AND tab_e.id3 = tab_f.id3 LIMIT 1; The above query now generates this error: Unknown column 'tab_a.id in 'on clause'. Placing parentheses around the ON clause, LEFT JOIN db.tab_b ON (tab_b.id = tab_a.id) does not work. However, splitting the original query apart and grouping the Left Joins in one query and the regular joins in another query does NOT generates any errors: SELECT tab_a.item1, tab_b.item2, tab_c.item3, tab_d.item4 FROM db.tab_a LEFT JOIN db.tab_b ON tab_b.id = tab_a.id LEFT JOIN db.tab_c ON tab_c.id = tab_a.id LEFT JOIN db.tab_d ON tab_d.id = tab_a.id WHERE tab_a.id = 'value' LIMIT 1; - this is ok AND SELECT tab_a.item1, tab_e.item5, tab_f.item6 FROM db.tab_a, db.tab_e, db.tab_f WHERE tab_a.id = 'value' AND tab_a.id2 = tab_e.id2 AND tab_e.id3 = tab_f.id3 LIMIT 1; - this is ok Why doesn't the original query work in MySQL 5.0? What do I need to do to make it work? The information you need is here: http://dev.mysql.com/doc/refman/5.0/en/join.html See particularly Join Processing Changes in MySQL 5.0.12 -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [5.0] Left Join Problem
Stephen P. Fracek, Jr. wrote: I'm trying to upgrade from MySQL 4.1 to MySQL 5.0. A query that works in MySQL 4.1 does not work in MySQL 5.0 and I'm at a loss as to how to proceed. SELECT tab_a.item1, tab_b.item2, tab_c.item3, tab_d.item4, tab_e.item5, tab_f.item6 FROM db.tab_a, db.tab_e, db.tab_f LEFT JOIN db.tab_b ON tab_b.id = tab_a.id LEFT JOIN db.tab_c ON tab_c.id = tab_a.id LEFT JOIN db.tab_d ON tab_d.id = tab_a.id WHERE tab_a.id = 'value' AND tab_a.id2 = tab_e.id2 AND tab_e.id3 = tab_f.id3 LIMIT 1; The above query now generates this error: Unknown column 'tab_a.id in 'on clause'. Placing parentheses around the ON clause, LEFT JOIN db.tab_b ON (tab_b.id = tab_a.id) does not work. However, splitting the original query apart and grouping the Left Joins in one query and the regular joins in another query does NOT generates any errors: SELECT tab_a.item1, tab_b.item2, tab_c.item3, tab_d.item4 FROM db.tab_a LEFT JOIN db.tab_b ON tab_b.id = tab_a.id LEFT JOIN db.tab_c ON tab_c.id = tab_a.id LEFT JOIN db.tab_d ON tab_d.id = tab_a.id WHERE tab_a.id = 'value' LIMIT 1; - this is ok AND SELECT tab_a.item1, tab_e.item5, tab_f.item6 FROM db.tab_a, db.tab_e, db.tab_f WHERE tab_a.id = 'value' AND tab_a.id2 = tab_e.id2 AND tab_e.id3 = tab_f.id3 LIMIT 1; - this is ok Why doesn't the original query work in MySQL 5.0? What do I need to do to make it work? TIA Stephen P. Fracek, Jr. [EMAIL PROTECTED] It is a join precedence issue. Use INNER Join instead of a comma. SELECT tab_a.item1, tab_b.item2, tab_c.item3, tab_d.item4, tab_e.item5, tab_f.item6 FROM db.tab_a INNER JOIN db.tab_e ON tab_a.id2 = tab_e.id2 INNER JOIN db.tab_f ON tab_e.id3 = tab_f.id3 LEFT JOIN db.tab_b ON tab_b.id = tab_a.id LEFT JOIN db.tab_c ON tab_c.id = tab_a.id LEFT JOIN db.tab_d ON tab_d.id = tab_a.id WHERE tab_a.id = 'value' LIMIT 1; -- Gerald L. Clark Supplier Systems Corporation -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [5.0] Left Join Problem
On 2006-07-21 1:01 PM, Gerald L. Clark [EMAIL PROTECTED] wrote: It is a join precedence issue. Use INNER Join instead of a comma. Thanks Gerald. Paul DuBois' polite suggestion to read the manual helped. Upon re-reading the section about the change in precedence with the comma operator and the join, I realized there was a simple fix and that I had misinterpreted the section on the first read. The revised query works. Stephen P. Fracek, Jr. [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
