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

[account8226]

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]

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!

 

[account8226]

There is a mysql error, but I understand where to have a look at Thanks for the help dude !

 

[account8226]

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

 

[account8226]

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]

That is probably because of field names overlapping.

Instead of selecting *, try selecting the fields you want precisely, and give them distinct names:

SELECT user1.username AS username_1, yourtable.yourfield1, yourtable.yourfield2, user2.username AS username_2 FROM...

 

[account8226]

That is probably because of field names overlapping.

Instead of selecting *, try selecting the fields you want precisely, and give them distinct names:

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.

 

[account8226]

That is probably because of field names overlapping.

Instead of selecting *, try selecting the fields you want precisely, and give them distinct names:

SELECT user1.username AS username_1, yourtable.yourfield1, yourtable.yourfield2, user2.username AS username_2 FROM...

Yeah I got it mate ! Thanks again !!