Perhaps a GROUP BY issue going from MySQL 7.# to 8.#...

[Jim R]

For four years the query below worked for a Function I wrote.   Players being recruited for college basketball.  They get scholarship offers from colleges.  Some of them get so many, toward the end they reduce it to a smaller list.   Then they commit.  This Function is then used/referred to in several places on the site.  

As MySQL upgraded to 8.#, it broke.  

I'm getting a Syntax error at line 3, which is the first GROUP_CONCAT.

		$query = "SELECT *,p.id as pid,po.playerID as offerID,recruit_type

					GROUP_CONCAT(
						CASE
							WHEN recruit_type = 'Offer' THEN c.college					
						END) as offers,
					GROUP_CONCAT(
						CASE
							WHEN recruit_type = 'List' THEN c.college		
						END) as list,
					CASE
 							WHEN other !='' THEN concat(c.college,' (',other,')')
 							WHEN recruit_type = 'Commit' THEN c.college
						END	as commit			

					
						
				FROM a_players p
				
				LEFT JOIN a_schools s
					ON p.schoolID = s.id

				LEFT JOIN a_players_offers po
					ON po.playerID = p.id
		
				LEFT JOIN a_colleges c
					ON po.collegeID = c.id
				
			WHERE p.id = '". $pid ."'";

 

[requinix]

It worked for years even though it's clearly missing a comma at the end of the first line?

[Jim R]

Yeah, I found it.   Not sure what I did to delete it.