here’s your last query, slightly reformatted…
SELECT *
FROM scms_module_events_registrations
INNER
JOIN scms_module_events
ON [COLOR="Blue"]scms_module_events.ID = scms_module_events_registrations.eID[/COLOR]
INNER
JOIN scms_module_events_cats_details
ON [COLOR="blue"]scms_module_events_cats_details.cID = scms_module_events.cID[/COLOR]
INNER
JOIN scms_module_events_invoices
ON scms_module_events_invoices.iID = '425'
INNER
JOIN scms_profiles
ON [COLOR="Blue"]scms_profiles.ID = scms_module_events_registrations.pID[/COLOR]
AND [COLOR="Red"]scms_module_events_registrations.ID = scms_module_events_invoices.rID[/COLOR]
AND [COLOR="Blue"]scms_profiles.lcode = scms_module_events_cats_details.lcode[/COLOR]
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)
here’s the query with the condition moved to the proper join…
SELECT *
FROM scms_module_events_registrations
INNER
JOIN scms_module_events
ON scms_module_events.ID = scms_module_events_registrations.eID
INNER
JOIN scms_module_events_cats_details
ON scms_module_events_cats_details.cID = scms_module_events.cID
INNER
JOIN scms_module_events_invoices
ON scms_module_events_invoices.rID = scms_module_events_registrations.ID
AND scms_module_events_invoices.iID = '425'
INNER
JOIN scms_profiles
ON scms_profiles.ID = scms_module_events_registrations.pID
AND scms_profiles.lcode = scms_module_events_cats_details.lcode
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 –
SELECT *
FROM scms_module_events_invoices
INNER
JOIN scms_module_events_registrations
ON scms_module_events_registrations.ID = scms_module_events_invoices.rID
INNER
JOIN scms_module_events
ON scms_module_events.ID = scms_module_events_registrations.eID
INNER
JOIN scms_module_events_cats_details
ON scms_module_events_cats_details.cID = scms_module_events.cID
INNER
JOIN scms_profiles
ON scms_profiles.ID = scms_module_events_registrations.pID
AND scms_profiles.lcode = scms_module_events_cats_details.lcode
WHERE scms_module_events_invoices.iID = '425'
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