I am far from being the best at SQL, and although I can get things done, they are far from being the best method. Currently, I have a SQL query which merges two tables and then loops through the results and makes a lot of other database calls. I am in the developing stages, but when I go live, this is a serious issue and I've been told that it is possible to do all of what I want to receive in a single query.
Sport Games Table
================================================================================================== | sport_id | sport_type_id | time_started | time_ended | initiated_by | triggered_by | |================================================================================================| | 1234 | 2 | 1640456916 | 1641148216 | 1152 | 35272 | ==================================================================================================
Sport Types Table
================================== | sport_type_id | sport_name | |================================| | 1 | tennis | | 2 | football | | 3 | golf | ==================================
I have been able to join these two tables together quite easily using the
JOIN feature like so:
$fetch_sports_games = new Database(); $fetch_sports_games->query("SELECT * FROM sports_games INNER JOIN sport_strut ON sports_games.sport_type_id = sport_strut.sport_type_id "); $sports_games = $fetch_sports_games->resultset();
I also want to fetch participents from the
sport_participents table which simply has the
user_id in. There are anywhere from 4 to 15 participents and I want them all in the same column in the result. I have found that
STRING_AGG( ... ) should be what I am looking for, however all my attempts have resulted in errors and I am just not sure whether I need to update my SQL server.
My last part of this, something which i cannot figure out the lodgic on how to do, once I can merge the participents, although this may need to come before, is that I would like to look up the
user_basic table for the
user_id and obtain their
user_name. This goes for all the participents, the
initiated_by and the
triggered_by , which are all
My attempts outside of looking into
STRING_AGG( ... ) have resulted in duplicated rows for the same amount of participents with one participent per row. I have attempted the group method too, but I am yet to see this work, or I am not understanding how that works correctly.
This is the most advanced SQL query I am attempting to develop, so any help in the right direction is extremely appreciated. If I need up update my server, I will do so for a more modern solution too.
If I can improve on my question, please let me know and I apologise if this is the case!
Thank you all!