SQL- using WHERE for non-related table

Question

I want to return defined results from member_network table using WHERE clause by team_id = 91 using JOIN.

Main table member_network structure look like:

| id |  network_profile_name |                              
|----------------------------|
|  1 |    John Doe           |

I have two fields in two more connected tables with the values I need.

Table team_member_network structure looks like:

| id | team_member_id | member_network_id |
|----|----------------|-------------------|
|  2 |             1  |                 1 |

Table team_member:

| id    | team_id | member_id |
| ------|---------|-----------|
| 1     |      91 |   1679817 |   

This is some kind of reverse relationship

My work so far:

    SELECT
        network_profile_name
    FROM member_network
    LEFT JOIN team_member_network ON team_member_network.team_member_id = team_member.team_id
     WHERE team_id = 91;

With an error:

missing FROM-clause entry for table "team_member" LINE 9: team_member_network ON team_member_network.team_member_id = team_member

What is problem here is that I do not know how to select teamId in member_network table as it is located in team_member table which I need to get through team_member_network.team_member_id and get to team_member.team_id

Should I use one more JOIN?

Answer 1

 SELECT
        network_profile_name
    FROM member_network as mn
    LEFT JOIN team_member_network tmn ON mn.id = tmn.member_network_id
    LEFT JOIN team_member tm ON tm.id = tmn.team_member_id
     WHERE team_id = 91;

should work

Answer 2

These three tables are part of a common data design pattern: many-to-many relationships. In your case you have a many-to-many relationship between your member_network entities (rows) and your team_member entities.

Your team_member_network table is sometimes called a join table, because it represents how to join your entities. It holds the relationships.

To use such a many-to-many relationship you do this:

SELECT member_network.network_profile_name,
       team_member.team_id
  FROM member_network
  JOIN team_member_network
        ON member_network.id = team_member_network.member_network_id
  JOIN team_member
        ON team_member_network.team_member_id = team_member.id
 WHERE team_member.team_id = 91;

The overall pattern is this:

    FROM EntityA
    JOIN JoinTable ON EntityA's ID
    JOIN EntityB ON EntityB's ID

If you want to see rows from your first table (member_network) without matching rows in your second table (team_member) use LEFT JOINs instead of ordinary inner JOINs.