SQL - Inner join on two columns from table 1 with one column from table 2?

inner-join, sql

Solution

You should join members table for twice.

SELECT M1.NAME , M2.NAME
FROM MEMBERS M1
INNER JOIN MATCH M
   ON M1.USER = M.USER
INNER JOIN MEMBERS M2
   ON M2.USER = M.MATCH

Be carefull with lower and upper identifiers if your database is mysql.

Problem

I have a table that has two columns that each contain a member id that is a foreign key to a members table that contains their name. I want to select the first table with names instead of member IDs. I'm not sure of a way to do this. I feel like there's certainly a way involving an INNER JOIN, but I can't think of how to pick two names from one table in one INNER JOIN. Any ideas? Thanks in advance! Match table ``` |------|-------| | user | match | |------|-------| | 1 | 4 | | 2 | 1 | | 3 | 2 | |------|-------| ``` Members table ``` |------|-------| | user | name | |------|-------| | 1 | Joe | | 2 | Kyle | | 3 | John | | 4 | Nate | |------|-------| ``` Desired output ``` |------|-------| | user | match | |------|-------| | Joe | Nate | | Kyle | Joe | | John | Kyle | |------|-------| ```

Original source