SQLTeam.com | Weblogs | Forums

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

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!

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

Did you notice that?

I found this under "Plesk Components"

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.

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.

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