Get all Many:Many relationships from reference/join-table

I am having difficulty querying for possible relationships in a Many:Many scenario.

I present my schema:

What I do know how to query with this schema is:

• All Bands that a given User belongs to.
• All Users that belong to a given Band.

What I am trying to do is:

• Get all Band Members across all Bands that a given User belongs to.
• ie, say I am in 5 bands, I want to know who all of my bandmates are.

My first questions are:

• Is there a name for this type of query? Where I am more interested in the joined relationships than what I am joined to (just saying that made me want to put this whole system into a Graph DB :/ )? I’d like to learn proper terminology to help me google for problems down the road.
• Is this a terrible idea in RDBMS land in general? I feel like this should be a common use case but I want to know if I’m totally approaching this wrong.

To recap:

• I am looking to query the above schema with the expected output being one row per User as Band Members that a given User shares a Band with.

Answer

Terminology

Your terminology seems to be correct – “many to many”, often written as “many:many” with a colon. Sometimes the middle table (band_members) is called the “bridge table”.

You can probably drop band_members.id, since the two foreign keys also make up a composite primary key (and the primary key can actually be defined that way, since normally a User cannot be a member of the same Band twice. The only exception to that is if a User could have more than one role in the same Band).

Solution

On the surface of it, this sounds easy – we can see the relationships of the tables, and one would normally just use an INNER JOIN between them. There are three tables, so that would be two joins.

However, we have to conceptualise the problem correctly first. The problem we have is that the join between Users and Band Members (user ID) is actually to be used for two things:

• which User is in what Band
• filtering by User

So to do this we need to introduce one table with multiple purposes:

SELECT
    Users.first_name, Users.last_name
FROM Users
INNER JOIN Band_Members Band_Members1 ON (Band_Members1.user_id = Users.Id)
INNER JOIN Band_Members Band_Members2 ON (Band_Members1.band_id = Band_Members2.band_id)
WHERE
    Band_Members2.user_id = 1

You can see here that I have joined Band_Members twice, and when one does that, one has to alias them differently, so they can be separately referenced. The first instance does the obvious join between the Users table and the bridge table, and the second one does a link between “Users who are in Bands” and “Bands that I am in”.

Of course, this solution requires that you know your User ID. If you had wanted to do a similar query but filter based on your name, then you would have to join to another (re-aliased) copy of the User table, so that you can differentiate between the two different purposes: “Users who are in bands” and “your User”.