Cannot use CTEs for some reason, so how to use subqueries instead, for MySQL RDS on AWS-CodePudding

I'm working with AWS RDS MySQL and using MySQL Workbench to develop the queries before moving them into the Lambda integration function for my HTTP API on AWS API Gateway. I created this query:

use prod_esports;
WITH 
muuid AS (select * from game_match where uuid = '2e4f899a-d690-4d41-8c31-c9f89e6a2e4d'),
teamID AS (SELECT id FROM team WHERE uuid = muuid.team_a_uuid),
SELECT * FROM team_member WHERE team_id = teamID;

MySQL Workbench says this is not supported on my server. So I need to use subqueries is probably the alternative. FYI:

muuid is a single result
teamID is a single result

I tried this:

use prod_ugcesports;
SELECT * FROM team_member WHERE team_id = 
(SELECT id FROM team WHERE uuid =
(SELECT * FROM game_match WHERE uuid = '2e4f899a-d690-4d41-8c31-c9f89e6a2e4d').team_a_uuid)

I cannot use the .team_a_uuid on the subquery.

Can anyone suggest the proper subqueries, or a better approach that AWS RDS will accept?

Many thanks!

CodePudding user response：

You don't need CTE or subqueries for this.

SELECT tm.*
FROM game_match AS gm
JOIN team AS t ON t.uuid = gm.team_a_uuid
JOIN team_member AS tm ON tm.team_id = t.id
WHERE gm.uuid = '2e4f899a-d690-4d41-8c31-c9f89e6a2e4d'