skip to Main Content

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?

2

Answers


  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

    Login or Signup to reply.
  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.

    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search