SitePoint Sponsor

User Tag List

Results 1 to 6 of 6

Hybrid View

  1. #1
    SitePoint Enthusiast
    Join Date
    Aug 2009
    Location
    Limbo
    Posts
    41
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    SQL Join getting obtaining data for 2 users from an event.

    Hey I have trouble joining tables in a way that I can obtain profile info from an event. Allow me to clarify. My website has an event table: user1(int), user2(int), event_type(int). Events join two users together.

    Now, from this standpoint the data I have is the user ID. The user ID can either be at user1 or user2. Though I want to join the user table to obtain information for both users.

    here is my statement:

    Code MySQL:
    SELECT * FROM users, events WHERE (events.user1 = $id or events.user2 = $id) AND events.user1 = users.profile AND events.user2 = users.profile.
    I know I'm not doing this right but I don't understand how else I can obtain the data of both users from the event table... I think I'm leaving out a logic or function. How can this be done?

    Any help in the right direction is greatly appreciated.

    Thanks

  2. #2
    SitePoint Enthusiast
    Join Date
    Aug 2009
    Location
    Limbo
    Posts
    41
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I have also tried this logic with no luck.

    SELECT * FROM owes FULL OUTER JOIN ON events.user1 = users.profile AND events.user2 = users.profile WHERE event.user1 = 1 or event.user2 = 1

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,260
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Jaipai View Post
    Though I want to join the user table to obtain information for both users.
    this is a common issue

    you will have to join to the users table twice, once for each user id

    you need table aliases, to distinguish between the two copies of the table, and also column aliases, to distinguish which column belongs with which user

    Code:
    SELECT events.event_type
         , user1.avatar AS user1_avatar
         , user2.avatar AS user2_avatar
      FROM events
    INNER
      JOIN users AS user1
        ON user1.profile = events.user1
    INNER
      JOIN users AS user2
        ON user2.profile = events.user2
     WHERE $id IN ( events.user1 , events.user2 )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Enthusiast
    Join Date
    Aug 2009
    Location
    Limbo
    Posts
    41
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I'm having a bit of trouble with the logic behind it but I'm starting to understand most of it. My question is in the table aliases where are you getting .avatar? Are they just defined by the creator? ANd if so, why must we also include user1.avatar AS user1_avatar?

    Additional info would be greatly appreciated.


    The rest of the query seem easily comprehensible and I thank you.

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,260
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    i chose avatar as a potential representive data column in the users table

    you said you wanted to join to the users table for each of the two user ids, and presumably you wanted to do this because you wanted some other information besides just the user id

    i had nothing to go on so i guessed, because you had used the dreaded, evil "select star"

    the reason for giving user1.avatar the alias user1_avatar and user2.avatar the alias user2_avatar is because without doing that, you'd have two columns in the result set called avatar, and then it's tricky to distinguish the two columns when it comes to putting them onto the web page
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Enthusiast
    Join Date
    Aug 2009
    Location
    Limbo
    Posts
    41
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    i chose avatar as a potential representive data column in the users table

    you said you wanted to join to the users table for each of the two user ids, and presumably you wanted to do this because you wanted some other information besides just the user id

    i had nothing to go on so i guessed, because you had used the dreaded, evil "select star"

    the reason for giving user1.avatar the alias user1_avatar and user2.avatar the alias user2_avatar is because without doing that, you'd have two columns in the result set called avatar, and then it's tricky to distinguish the two columns when it comes to putting them onto the web page

    Thank you very much. I understand now changed it to fit my page. Ever grateful.


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
  •