Mysql Question posted in
The official MySQL documentation can be found here.

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

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!

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    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';

  2. 0

    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'
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search