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?
CodePudding user response:
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
CodePudding user response:
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.