php - 如何成功完成此INSERT JOIN WHERE SQL语句？

我希望在表community_players中插入来自各个表的community_id和player_id；“communities”和“users”，其中满足以下条件：
users.user_email AND communities.admin='[email protected]'
以及
communities.code='HX99F9'
这就是我目前所拥有的。。。。
INSERT INTO TABLE community_players (community_id, player_id)FROM TABLE communitiesJOIN TABLE usersON idWHERE users.user_email = '[email protected]'AND communities.admin = '[email protected]'AND communities.code = 'HX99f9'
以下是所讨论的3张表：
usersid | user_email | user_password | user_name
communitiesid | name | code | admin
community_playerscommunity_id | player_id

最佳答案

应该是这样的：

INSERT INTO community_players (community_id, player_id)
SELECT communities.id, users.id
FROM communities INNER JOIN users ON communities.admin = users.user_email
WHERE users.user_email = '[email protected]'
AND communities.code = 'HX99f9'