mysql - 在 MySQL 中获取特定团队中的团队成员

Question

我有这个团队表(也可以是项目表)和成员表

+-----------+--------------+------+-----+---------+----------------+
| Field     | Type         | Null | Key | Default | Extra          |
+-----------+--------------+------+-----+---------+----------------+
| id        | int(11)      | NO   | PRI | NULL    | auto_increment |
| team_name | varchar(100) | NO   | UNI | NULL    |                |
| team_desc | varchar(200) | NO   |     | NULL    |                |
| createdby | varchar(100) | NO   |     | NULL    |                |
+-----------+--------------+------+-----+---------+----------------+

和成员表

+-------------+--------------+------+-----+---------+----------------+
| Field       | Type         | Null | Key | Default | Extra          |
+-------------+--------------+------+-----+---------+----------------+
| id          | int(11)      | NO   | PRI | NULL    | auto_increment |
| firstname   | varchar(45)  | NO   |     | NULL    |                |
| lastname    | varchar(45)  | NO   |     | NULL    |                |
| address     | varchar(200) | NO   |     | NULL    |                |
| city        | varchar(45)  | NO   |     | NULL    |                |
| state       | varchar(45)  | NO   |     | NULL    |                |
| country     | varchar(45)  | NO   |     | NULL    |                |
| email       | varchar(45)  | NO   |     | NULL    |                |
| phone       | varchar(10)  | YES  |     | NULL    |                |
| experience  | varchar(45)  | NO   |     | NULL    |                |
| designation | varchar(45)  | NO   |     | NULL    |                |
| username    | varchar(45)  | NO   |     | NULL    |                |
| password    | varchar(45)  | NO   |     | NULL    |                |
+-------------+--------------+------+-----+---------+----------------+

这两个表通过此 groups_members 表连接起来，

+------------------+---------+------+-----+---------+----------------+
| Field            | Type    | Null | Key | Default | Extra          |
+------------------+---------+------+-----+---------+----------------+
| teams_members_id | int(11) | NO   | PRI | NULL    | auto_increment |
| teams_id         | int(11) | NO   | MUL | NULL    |                |
| members_id       | int(11) | NO   | MUL | NULL    |                |
+------------------+---------+------+-----+---------+----------------+

我想做的是让成员加入特定的团队。我很困惑我要如何做到这一点(也许我很愚蠢:P)

SELECT * 
FROM teams_members tm, members m, teams t 
WHERE tm.members_member_id = m.id 
AND t.team_name='team name can be anything'
AND tm.id = t.id

返回 0 个结果。我哪里可能错了？

Answer 1

在您的查询中，与 team_member 和 team 的连接不正确:

试试这个:

SELECT * 
FROM teams_members tm, members m, teams t 
WHERE tm.members_member_id = m.id 
AND t.team_name='team name can be anything'
AND tm.teams_id = t.id

或

SELECT * 
FROM teams_members tm
inner join members m on tm.member_id=m.id
inner join teams t on tm.teams_id=t.id
WHERE 
t.team_name='team name can be anything'