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 ... ))

13.2.8.2 join syntax

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

Popular posts from this blog

jquery - How do you format the date used in the popover widget title of FullCalendar? -

asp.net mvc - SSO between MVCForum and Umbraco7 -

Python Tkinter keyboard using bind -