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]
