SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Addict
    Join Date
    Jul 2013
    Location
    South of the equator, then turn left
    Posts
    329
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Join multiple tables - query not working if problem with foreign keys

    Hi everyone,

    please excuse the convoluted explanation. Hopefully someone can make sense of it.

    I'm joining a number of tables. I've noticed that the query fails completely if I don't set all of the foreign keys properly. For example, table3 might have a column (id_two) which contains the foreign keys of another table. Now I need to manually add a number (key) to each field in the id_two column in table 3. The primary keys range from 1 upwards. If I add a 0 instead, the query will fail. Of course I understand that the join between the two tables will no longer work. What I don't understand is why the remainder of the joins don't continue to work. Does one JOIN failure break the query?

    Thanks for your help!!!

    PHP Code:
    $q "SELECT  a.column1, b.column1, b.column2, b.column3, c.column1, c.column2, d.column1  FROM table1 AS a 
    INNER JOIN table2  AS b USING (id_one) 
    INNER JOIN table3 AS c USING (id_two) 
    INNER JOIN table4 AS d USING (id_three)   
    WHERE b.column1 = '
    $name' "

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by RedBishop View Post
    Does one JOIN failure break the query?
    of course
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Addict
    Join Date
    Jul 2013
    Location
    South of the equator, then turn left
    Posts
    329
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi there Rudy,

    thank you for the confirmation! I was hoping you'd say the other joins would still work, but alas...

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    technically, they are all working just fine, just not returning what you hoped for

    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
  •