mySQL query to gather 2 ids and match them to 2 names per row-CodePudding

Hey all I have an SQL query problem that I have been trying to wrap around my head for a few now and just can not come up with the correct query in order to execute it.

What I am wanting to do is look into 2 tables. Each table has a unique id that matches each other and then a name associated with that id. However, the OtherTbl has both IDs in the same record instead of separate like its in the UserTbl.

UserTbl:

id | mID   | name
---|-------|-------------
5  | 12345 | Bob Barker
6  | 54688 | Steve Jobs
7  | 56999 | Adam Sandler
9  | 53166 | Bill Gates
11 | 87540 | MC Hammer

OtherTbl:

id | leftID | rightID
---------------------
8  | 54688  | 12345
41 | 87540  | 87540
43 | 56999  | 53166

So what I wanting to get out of those 2 tables above are the 2 users that are together (leftID & rightUD). Something like the below for the output:

leftID | rightID | leftName     | rightName
-------|---------|--------------|-----------
54688  | 12345   | Steve Jobs   | Bob Barker
87540  | 87540   | MC Hammer    | MC Hammer
56999  | 53166   | Adam Sandler | Bill Gates

I know this can be done - I just cant think of a way in order to make it work!

I tried the tride-and-true inner join's

SELECT 
    ut.leftID,
    ut.rightID,
    ot.leftName 
FROM 
    OtherTbl as ot
INNER JOIN 
    UserTbl AS ut 
ON
    ot.leftID = ut.mID

This query above does output the data but its not tied together like in my example output above. Not to mentioned that its leaving the users that are on the rightUserFbID.

An SQL guru would be great to show me what I am missing (or forgetting about being able to use)!

CodePudding user response：

You'll need to use the usertbl table twice - once for leftname (alias a1), and another time for rightname (alias a2).

SQL> select b.leftid, b.rightid, a1.name as leftname, a2.name as rightname
  2  from othertbl b join usertbl a1 on a1.mid = b.leftid
  3                  join usertbl a2 on a2.mid = b.rightid;

    LEFTID    RIGHTID LEFTNAME     RIGHTNAME
---------- ---------- ------------ ------------
     54688      12345 Steve Jobs   Bob Barker
     56999      53166 Adam Sandler Bill Gates
     87540      87540 MC Hammer    MC Hammer

SQL>