MySQL Forums
Forum List  »  Newbie

guidance on SQL query
Posted by: Michael Williams
Date: August 09, 2022 05:26PM

Hi, I'm using AWS RDS MySQL 8.0.23 on Linux, and mysql workbench 8.0.30. I'm trying to generate results that combine data from multiple tables into a single row in the results. There are three tables involved, let's call them match and teammember, and user. I need to return a few fields from the match table, along with an array of the team members from the two teams playing in the match. The data is not stored in JSON in the tables. The format of the results as expressed in JSON should be:

{
"match" {
"matchid": "string",
"team1id": "string",
"team2id": "string",

},
"teammembers" {
"team1": [
{
"userinfo": {
"userid": "string",
"fullname": "string"
},
"picture": "string"
},
{
"userinfo": {
"userid": "string",
"fullname": "string"
},
"picture": "string"
}
],
"team2": [
{
"userinfo": {
"userid": "string",
"fullname": "string"
},
"picture": "string"
},
{
"userinfo": {
"userid": "string",
"fullname": "string"
},
"picture": "string"
}
]
}
}


The match table has matchid, team1id and team2id. The teammeber table has teamid and userid. The user table has userid, fullname and picture.

I cannot change the structure of the existing tables. So I'm trying to understand the appropriate query to create this row with the above format. I want to have a row for each match, with the corresponding team members info, structured as above. I suppose I could have multiple select statements and glue the results together. Is there a more sophisticated way to get the results? Many thanks!

Options: ReplyQuote


Subject
Written By
Posted
guidance on SQL query
August 09, 2022 05:26PM
August 10, 2022 04:22AM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.