Merging Several Tables, Combining Rows to One Column, STRING_AGG( ... ) not working?

1

Hello Everyone!

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 sport_id and 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.

Plesk shows:
mysql 5.5.68-1.el7

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 user_id's.

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!

2

pictures are good, but it's alot easier if you supply readily consumable DDL, sample data and expected results. You can use stuff for this

create table #Sports (sport_id  int,   sport_type_id  int,   time_started  int,   time_ended  int,   initiated_by  int,   triggered_by  int) 
insert into #Sports values (1234    ,        2        ,   1640456916   ,  1641148216  ,     1152       ,     35272   )

Create table #SportsType ( sport_type_id  int,   sport_name  varchar(20))
insert into #SportsType values
(1,' tennis'),
(2,'football'),
(3,'  golf')

create table #SportsParticipants (sport_id  int,   Userid int) 
insert into #SportsParticipants values
(1234,1),
(1234,2),
(1234,3),
(1234,4)

Select s.Sport_ID, st.sport_name, s.time_started, s.time_ended, s.triggered_by , 
		STUFF( 
				(       SELECT '-' + cast(Userid as varchar(20))
						FROM #SportsParticipants t2  
						WHERE s.sport_id=t2.sport_id  
						FOR XML PATH('') 
				), 
				1, 
				1,'' 
			  ) AS t
  from #Sports s
	join #SportsType st
		on s.sport_type_id = st.sport_type_id
3

Did you notice that?

4

I found this under "Plesk Components"

image
image1667×521 19.3 KB

5

Sorry about not replying sooner!

I've beencalled away from my PC all day andI am yet to respond fully.

Just want to show some acknoledgement and appreciation, and I should be able to fully look into what you have said soon.

6

My reply was actually to Mike. I'm not sure he realized this was for MySQL and not SQL Server. TIMINION: please keep in mind that this is a SQL Server forum, so people here can provide advanced SQL Server T-SQL, but may not be able to provide advanced MySQL code.

7

Oh, I'm sorry. I did not realise that!