Jason Hoffmann Jason Hoffmann - 1 year ago 69
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 Source

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,
FROM Schedule s
    ON s.userA_id = u1.id
    ON s.userB_id = u2.id
WHERE s.week_id = 1
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download