mysql - LEFT JOIN on multiple tables in "FROM" part -
i'm unable find exact example on stackoverflow, here problem. in mysql 4.x works fine, 5.x seems not following:
select stuff.. schools, member , applications left join courses on courses.id = applications.course_id left join staff on staff.id=member.staff_id
this section throws error saying "unknown column 'member.staff_id' in 'on clause'". how change 5.x won't complain?
edit: did notice when using 'one' left join, did matter table name placed in part, ie has @ end. not sure has relevance problem though. issue 5.x
according official mysql doco, precedence of join
operator higher of ,
(comma) operator. cause joins
evaluated without member
table, meaning member
not available in join
. because applications
table last in series of comma delineated tables, available joins. mysql sees tables this: (schools, member, (applications left join courses ... ))
however, precedence of comma operator less of inner join, cross join, left join, , on. if mix comma joins other join types when there join condition, error of form unknown column 'col_name' in 'on clause' may occur.
the reason encountering error now, whereas able before, (from same link)
previously, comma operator (,) , join both had same precedence.
to around order of operations limitation, can either rewrite query using explicit join syntax only, or can force grouping of comma separated tables surrounding them parenthesis, make mysql see tables more ( (schools, member, applications) left join courses ... )
select stuff.. (schools, member , applications) left join courses on courses.id = applications.course_id left join staff on staff.id=member.staff_id
but i'd still recommend using explicit join syntax.
select stuff.. schools join member join applications left join courses on courses.id = applications.course_id left join staff on staff.id = member.staff_id
Comments
Post a Comment