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!
>Solution :
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'