SitePoint Sponsor

User Tag List

Results 1 to 9 of 9

Hybrid View

  1. #1
    SitePoint Addict
    Join Date
    Feb 2004
    Location
    Netherlands
    Posts
    381
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    INNER JOIN stopped working?

    why does:
    Code:
    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. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,021
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    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
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Addict
    Join Date
    Feb 2004
    Location
    Netherlands
    Posts
    381
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I rewrote it successfully to:
    Code:
    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

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,021
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    u rewrote it wrong

    please indicate which tables these unqualified columns belong to: rID, iID, eID, pID
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Addict
    Join Date
    Feb 2004
    Location
    Netherlands
    Posts
    381
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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?

  6. #6
    SitePoint Addict
    Join Date
    Feb 2004
    Location
    Netherlands
    Posts
    381
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I rewrote it to:
    Code:
    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...

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,021
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    here's your last query, slightly reformatted...
    Code:
    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
    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...
    Code:
    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 --
    Code:
    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

    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Addict
    Join Date
    Feb 2004
    Location
    Netherlands
    Posts
    381
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,021
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    you're welcome, and thanks for the kind words about the book
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •