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

[Jaipai]

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:

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

[Jaipai]

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

[r937]

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

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 )

[Jaipai]

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.

[r937]

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

[Jaipai]

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.