Jason Hoffmann Jason Hoffmann - 2 months ago 10
MySQL Question

Pulling multiple rows from the same table from a JOIN table in MySQL

I have a join table for a schedule (weeks 1, 2, 3, etc) that connects users. Each week in the join table has IDs for two separate users, User A and User B. But there are also multiple rows for each week. So it looks like this:

Schedule Table:

-----------------------------------
| week_id | userA_id | userB_id |
-----------------------------------
| 1 | 1 | 2 |
| 1 | 2 | 3 |
----------------------------------


I also have a user table which stores all the information about a user, pretty basic stuff. So, for instance:

User Table:

-----------------------------------
| id | Name | Nickname |
-----------------------------------
| 1 | jason | jay |
| 2 | billy | bill |
----------------------------------


What I'd like to do is query my schedule table based on the Week ID and then return a series of users for each Week. So for instance, give me all the users from week 1 in a series of objects, something like [ { UserA.name: jason, UserB.name: billy }, { UserA.name : billy, UserB.name: jane } ], etc.

I have a sneaking suspicion that this will include multiple JOIN statements, but I can't seem to get it working. Does anyone know how to structure this MySQL statement?

Answer

One option here is just to join the Schedule table twice to the User table, one join for each user in the relationship.

SELECT u1.Name,
       u2.Name
FROM Schedule s
INNER JOIN User u1
    ON s.userA_id = u1.id
INNER JOIN User u2
    ON s.userB_id = u2.id
WHERE s.week_id = 1