join 2 tables with 3rd table

Discussion in 'MySQL' started by sensoryaddict, May 2, 2011.

0
  1. #1
    Hello

    I have 3 tables. What is the best statement to join the 3 tables. I have tried have tried similar statements I have used to join 2 tables, but when the 3rd table is involved I'm getting lost. Thanks in advance. MySQL view statement.


    users
    -id
    -username


    profiles
    -id
    -first
    -last


    userprofiles
    -userid
    -profileid
     
    sensoryaddict, May 2, 2011 IP
  2. jkl6

    jkl6 Peon

    Messages:
    70
    Likes Received:
    1
    Best Answers:
    0
    Trophy Points:
    0
    #2
    Here you go:

    select *
    from users u, profiles p, userprofiles up
    where up.userid=u.id and up.profileid=p.id
     
    jkl6, May 2, 2011 IP
  3. jestep

    jestep Prominent Member

    Messages:
    3,659
    Likes Received:
    215
    Best Answers:
    19
    Trophy Points:
    330
    #3
    I like specifying join in the query. It's easier to see what's going on. If you have very complex queries, it gets nearly impossible to quickly figure out how the tables are joined when it is performed in the where clause.

    SELECT u.columns, p.columns
    FROM userprofiles up
    INNER JOIN users u ON up.userid = u.id
    INNER JOIN profiles p ON up.profileid = p.id
     
    jestep, May 3, 2011 IP
  4. ruvenf

    ruvenf Peon

    Messages:
    19
    Likes Received:
    1
    Best Answers:
    0
    Trophy Points:
    0
    #4
    I agree with jestep, join work much better.
    I think you just got the joins wrong it should be like this:

    SELECT * FROM users
    INNER JOIN profiles p ON users.id = p.id
    INNER JOIN userprofiles up ON users.id = up.userid
     
    ruvenf, May 3, 2011 IP