CODEWITHSUNDEEP
mysqlsql
Brian Moreno
Brian Moreno

Reputation: 1027

Join Tables On Two Columns

i've been trying to join to one table but using two different columns. Ok so i've got the following tables:

Users:

--------------------------------------------------
| user_id | name                                 |
--------------------------------------------------
| 1       | Bob                                  |
--------------------------------------------------
| 2       | John                                 |
--------------------------------------------------

Table2:

--------------------------------------------------
| user_one | user_two  | field3                  |
--------------------------------------------------
| 1        | 2         | something here          |
--------------------------------------------------

Is their any way to join the user's table using both fields. Something like this:

    SELECT 
     table2.*,
     users.name
    FROM table2
    INNER JOIN users
    ON users.user_id = table2.user_one AND users.user_id = table2.user_two

I've tried the above query but it doesn't return anything.

Edit I would want to return something like this:

--------------------------------------------------
| user_one_name | user_two_name  | field3        |
--------------------------------------------------
| Bob           | John           | something here|
--------------------------------------------------

Upvotes: 1

Views: 60

Answers (2)

sgeddes
sgeddes

Reputation: 62831

It sounds like you want to get the name of both users in a single row. Assuming so, then you can join to the users table multiple times:

SELECT 
    table2.*,
    u1.name user1name,
    u2.name user2name
FROM table2 t2
    INNER JOIN users u1 ON u1.user_id = t2.user_one
    INNER JOIN users u2 ON u2.user_id = t2.user_two

As mentioned in a comment, if it's possible you have user ids in table2 that do not exist in the users table (or you allow null values to be stored), you can use an outer join instead to get your desired results.

However, if that is the case, you may have larger issues with your database design. You shouldn't have foreign keys that don't have corresponding primary keys unless those are perhaps nullable.

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269443

You need two joins. This is often done using left join in case one or both user ids do not match:

SELECT t2.*, u1.name as user1name, u2.name as user2name
FROM table2 t2 LEFT JOIN
     users u1
     ON u1.user_id = t2.user_one LEFT JOIN
     users u2
     ON u2.user_id = t2.user_two;

Upvotes: 3

Related Questions