• This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn more.

SQL : Two user ids on the same row - how can I join xf_user for each one of them ?

A

account8226

Guest
#1
Hello XenForo,


Here is my problem,

I made my own SQL table, nothing special here.
But... in that table, their are two members per row (member_id_1, member_id_2, don't ask me why, it is needed for my add-on ;)).

When I select that row, I would like to join xf_user so I can use rich usernames as an example, for both users.

My question ? How could I select my row and join xf_user on each of the two members id.


Thanks you very much for your help.
 

ManOnDaMoon

Well-known member
#2
Code:
SELECT * FROM ((yourtable INNER JOIN xf_user AS user1 ON user1.user_id = yourtable.member_id_1) INNER JOIN xf_user AS user2 ON user2.user_id = yourtable.member_id_2);
Something along the lines of the above should do the trick. Untested!
 
A

account8226

Guest
#4
Code:
SELECT * FROM ((yourtable INNER JOIN xf_user AS user1 ON user1.user_id = yourtable.member_id_1) INNER JOIN xf_user AS user2 ON user2.user_id = yourtable.member_id_2);
Something along the lines of the above should do the trick. Untested!
Thanks again dude ! Merci beaucoup ;) I got it working thanks to your help, there was a little error on your query that I fixed with this : http://stackoverflow.com/questions/3031589/sql-select-multiple-inner-joins but thanks I was stuck for days now ;)
 
A

account8226

Guest
#5
Code:
SELECT * FROM ((yourtable INNER JOIN xf_user AS user1 ON user1.user_id = yourtable.member_id_1) INNER JOIN xf_user AS user2 ON user2.user_id = yourtable.member_id_2);
Something along the lines of the above should do the trick. Untested!
Huhh, doesn't works :(

In fact, your query is selecting my whole row, and only one of the two xf_user...
 

ManOnDaMoon

Well-known member
#6
That is probably because of field names overlapping.

Instead of selecting *, try selecting the fields you want precisely, and give them distinct names:
Code:
SELECT user1.username AS username_1, yourtable.yourfield1, yourtable.yourfield2, user2.username AS username_2 FROM...
 
A

account8226

Guest
#7
That is probably because of field names overlapping.

Instead of selecting *, try selecting the fields you want precisely, and give them distinct names:
Code:
SELECT user1.username AS username_1, yourtable.yourfield1, yourtable.yourfield2, user2.username AS username_2 FROM...
SELECT member1.username, member2.username [...]
is only returning one username (member's 2) actually.
 
A

account8226

Guest
#8
That is probably because of field names overlapping.

Instead of selecting *, try selecting the fields you want precisely, and give them distinct names:
Code:
SELECT user1.username AS username_1, yourtable.yourfield1, yourtable.yourfield2, user2.username AS username_2 FROM...
Yeah I got it mate ! Thanks again !!