1. 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 ?

Discussion in 'XenForo Development Discussions' started by account8226, Mar 11, 2013.

  1. account8226

    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.
  2. ManOnDaMoon

    ManOnDaMoon Well-Known Member

    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 likes this.
  3. account8226

    account8226 Guest

    There is a mysql error, but I understand where to have a look at ;) Thanks for the help dude !
    ManOnDaMoon likes this.
  4. account8226

    account8226 Guest

    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 ;)
    ManOnDaMoon likes this.
  5. account8226

    account8226 Guest

    Huhh, doesn't works :(

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

    ManOnDaMoon Well-Known Member

    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...
  7. account8226

    account8226 Guest

    is only returning one username (member's 2) actually.
  8. account8226

    account8226 Guest

    Yeah I got it mate ! Thanks again !!

Share This Page