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

gwhiz 0
Viewed 21

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!

common-table-expression subquery mysql
2 Answers

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'
Bill Karwin0

After @Bill Karwin gave a simple answer, I added on to the query to reach the specific user data for each team member following his pattern:

SELECT usr.*
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
JOIN user AS usr ON usr.id = tm.user_id
WHERE gm.uuid = '2e4f899a-d690-4d41-8c31-c9f89e6a2e4d';
gwhiz0
Related