SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Enthusiast
    Join Date
    Oct 2005
    Posts
    53
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question MySQL table relationship problem

    Hello,

    I have the following table:
    tableA (
    intUser1,
    intUser2
    );

    The values in the table are (1,2), (2,3) and (3,1).
    I would like to select intUserIds from table who are associated with 2.
    So in my case I would get the result 1 and 3.

    I used a query saying:
    SELECT intUserId1, intUserId2 FROM tableA WHERE intUserId1 = 2 OR intUserId2 = 2.
    The problem is that this query always returns two columns, one of them being value 2. How can I select only the columns associated with 2?

    Thank you in advance,
    Armin
    Online Games - Play Free Online Games
    Arcade Games - Tons of Arcade Games
    Watermark Tool - Protect your Photos

  2. #2
    SitePoint Wizard jimbo_dk's Avatar
    Join Date
    May 2005
    Location
    Singapore
    Posts
    1,261
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Try something like this:

    SELECT intUserId1 FROM tableA WHERE intUserId2 = 2
    UNION
    SELECT intUserId2 FROM tableA WHERE intUserId1 = 2
    Winners Respond. Losers React.
    Singapore Web Designer

  3. #3
    SitePoint Enthusiast
    Join Date
    Oct 2005
    Posts
    53
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Awesome. That certainly works.

    However is there another alternative where I can do it with one query instead of two? I mean, the union of two selects performs two queries doesn't it?

    Armin
    Online Games - Play Free Online Games
    Arcade Games - Tons of Arcade Games
    Watermark Tool - Protect your Photos

  4. #4
    SitePoint Guru
    Join Date
    Jan 2004
    Location
    Uppsala, sverige
    Posts
    697
    Mentioned
    2 Post(s)
    Tagged
    1 Thread(s)
    Code:
    select case when IntuserId1 = 2 then Intuserid2 else IntuserId1 end
      from tableA
     where 2 in (IntuserId1,IntuserId2)

  5. #5
    SitePoint Enthusiast
    Join Date
    Oct 2005
    Posts
    53
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Awesome man.

    I'll check it out.

    Thanks for all the help
    Online Games - Play Free Online Games
    Arcade Games - Tons of Arcade Games
    Watermark Tool - Protect your Photos

  6. #6
    SitePoint Enthusiast
    Join Date
    Oct 2005
    Posts
    53
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hello,

    I have another follow up question. This case has helped me retrieve the proper intUserId, but I need to now join this retrieved user id with other tables to get more data. When I tried this query, I get "Unknown column intFriendId"

    select ( case when intUserId= 1 then intUserd1 else intUserId2 end ) intFriendId
    from account a RIGHT JOIN relationship r
    ON a.intUserId = intFriendId
    where 1 in (intUserId1,intUserId2 )

    I assigned an alias to the whole ( case ) statement. the alias is intFriendId and I'm trying to use that id to join other tables so I can retrieve the data that is related to intFriendId.

    Thank You,
    Armin
    Online Games - Play Free Online Games
    Arcade Games - Tons of Arcade Games
    Watermark Tool - Protect your Photos

  7. #7
    SitePoint Guru
    Join Date
    Jan 2004
    Location
    Uppsala, sverige
    Posts
    697
    Mentioned
    2 Post(s)
    Tagged
    1 Thread(s)
    Code:
    select <columns>
      from relation as r left join
      (select 
        case when intUserId = 1 then intUserd1 
               else intUserId2 end) as intFriendId
         from account
       where 1 in (intUserId1,intUserId2)) as dt 
        ON r.intUserId = dt.intFriendId


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
  •