SitePoint Sponsor

User Tag List

Results 1 to 5 of 5

Thread: Table Join

  1. #1
    SitePoint Zealot
    Join Date
    Jul 2008
    Posts
    154
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Table Join

    Hi!

    I have Table 'user':
    id = 1, name = John
    id = 2, name = Peter
    id = 3, name = Adam

    Table 'usertofolder':
    fkuserid = 1, fkfolderid = 3

    Table 'folder':
    id= 2, foldername = 'folderB'
    id = 3, foldername = 'folderA'

    I want to have a select statement to list all the names in table 'user' and the name for folder.id = 3.

    Eg:
    John, folderA
    Peter, null
    Adam, null

    Is that possible?

    Thanks.

  2. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,397
    Mentioned
    147 Post(s)
    Tagged
    4 Thread(s)
    Yes. Use a LEFT JOIN

  3. #3
    SitePoint Zealot
    Join Date
    Jul 2008
    Posts
    154
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi!

    I have tried

    SELECT * FROM user
    LEFT JOIN usertouser ON user.id = fkuserid
    LEFT JOIN folder ON fkfolderid = folder.id
    WHERE folder.id = 3

    but it only gives me one name.

  4. #4
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,397
    Mentioned
    147 Post(s)
    Tagged
    4 Thread(s)
    Putting that condition on the folder table in the WHERE clause, makes the LEFT JOIN behave as an INNER JOIN.
    Put it in the ON clause instead:

    SELECT * FROM user
    LEFT JOIN usertouser
    ON user.id = fkuserid
    LEFT JOIN folder
    ON fkfolderid = folder.id
    AND folder.id = 3

  5. #5
    SitePoint Zealot
    Join Date
    Jul 2008
    Posts
    154
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    thanks a million


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
  •