throw this error?You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ON scms_module_events_registrations.ID = rID AND iID = '425'' at line 2

since 2 weeks, it worked like a charm earlier for almost a year. If something changed in MySQL, how to rewrite it?mysql v 5.0.67

the join conditions marked in blue are correct and very easy to understand

the one marked in red is problematic, because of where it is located, because it has nothing to do with the join that it is a condition of, if you know what i mean (this is also why you got the error message using the comma style of joins in your original query)

there's still something not quite right about this (to my eyes) and that's the scalar condition on the iID in the invoices table

i suspect what the query is doing is retrieving data for only one specific invoice, in which case that table should be the "driving" table (the one mentioned first in the FROM clause) with this condition placed in the WHERE clause --

the fact that mysql was able to run various versions of this query correctly simply underscores how intelligent the optimizer is

nevertheless, i believe in writing the FROM clause in such a manner that the joins make sense, each table being joined using conditions from that table matching columns from a previously-mentioned table in a logical sequence

this rigour in writing will pay off in spades when you come back to a query later and try to understand what it's doing

r937
—
2010-03-13T13:11:27Z —
#3

your query mentions multiple columns, some of which are qualified by their table names (e.g. scms_module_events_cats_details.cID) and some which are not (e.g. rID)