Jump to content

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


Recommended Posts

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 ."'";

 

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.