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
should work
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 yourteam_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:
The overall pattern is this:
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.