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

  • Thread starter Thread starter account8226
  • Start date Start date
A

account8226

Guest
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.
 
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!
 
There is a mysql error, but I understand where to have a look at ;) Thanks for the help dude !
 
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 ;)
 
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...
 
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...
 
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.
 
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 !!
 
Top Bottom