INNER JOIN stopped working?

Databases
1

why does:

SELECT * FROM `scms_module_events_registrations` INNER JOIN 
`scms_module_events_cats_details`,
`scms_module_events_invoices`,
`scms_module_events`,
`scms_profiles` 
ON 
scms_module_events_registrations.ID = `rID` AND 
`iID` = '425' AND 
scms_module_events_cats_details.cID = scms_module_events.cID AND 
scms_module_events.ID = `eID` AND scms_profiles.ID = `pID` AND 
scms_profiles.lcode = scms_module_events_cats_details.lcode

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

2

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

:slight_smile:

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)

please indicate which tables these unqualified columns belong to: rID, iID, eID, pID

4

I rewrote it successfully to:


SELECT * 
FROM `scms_module_events_registrations` 
INNER JOIN `scms_module_events` ON scms_module_events.ID = `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 `iID` = '425'
INNER JOIN `scms_profiles` ON scms_profiles.ID = `pID` 
AND scms_module_events_registrations.ID = `rID` 
AND scms_profiles.lcode = scms_module_events_cats_details.lcode

the eID, rID columns are unique so I guess that isn’t the error

5

u rewrote it wrong :slight_smile:

please indicate which tables these unqualified columns belong to: rID, iID, eID, pID

6

it didn’t throw up an error and gave me back the data I was looking for using phpmyadmin. Any idea why it did its job then?

7

I rewrote it to:

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.iID = '425'
INNER JOIN `scms_profiles` ON scms_profiles.ID = scms_module_events_registrations.pID
AND scms_module_events_registrations.ID = scms_module_events_invoices.rID
AND scms_profiles.lcode = scms_module_events_cats_details.lcode

it gave me the exact same results…

8

thank you for your feedback, clarifies the joins a bit more to me. I like your book by the way, it really helped to clarify how joins work in the first place, it couldn’t grasp that for several years :slight_smile:

9

you’re welcome, and thanks for the kind words about the book :slight_smile: