jaipai — 2012-08-23T18:49:51-04:00 — #1
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.
jaipai — 2012-08-23T19:11:07-04:00 — #2
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 — 2012-08-23T21:09:40-04:00 — #3
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
, <font color='"#0000FF"'>user1</font>.avatar <font color='"#FF0000"'>AS user1_avatar</font>
, <font color='"#0000FF"'>user2</font>.avatar <font color='"#FF0000"'>AS user2_avatar</font>
JOIN users <font color='"#0000FF"'>AS user1</font>
ON <font color='"#0000FF"'>user1</font>.profile = events.user1
JOIN users <font color='"#0000FF"'>AS user2</font>
ON <font color='"#0000FF"'>user2</font>.profile = events.user2
WHERE $id IN ( events.user1 , events.user2 )
jaipai — 2012-08-23T22:05:17-04:00 — #4
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 — 2012-08-24T00:19:27-04:00 — #5
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 — 2012-08-24T11:29:07-04:00 — #6
Thank you very much. I understand now changed it to fit my page. Ever grateful.
mittineague — 2014-09-18T23:25:20-04:00 — #7
This topic is now archived. It is frozen and cannot be changed in any way.