Table with one row joined to a table that produces multiple rows...

[Jim R]

I have a Player Profile that, among many other things, lists which scholarship offers he has.  This was the 'offers' column in a_players (a CSV list that I exploded and sorted), but I decided I wanted an Offers table.  It presumably will provide more flexibility in terms of how data is input and output.  

Many of the results from a_players (one row) will have about 5-6 rows from a_players_offers.  To keep from printing all the profile information for each row of offers the player had, I dug around and found group_concat, which then allowed me to explode the list and sort it, much like before.

I couldn't lump them all in one CASE command because offers can actually an offer or part of a list (fewer schools).

 

 

		$query = "SELECT *,p.id as pid,
			CASE
				WHEN recruit_type = 'List' THEN GROUP_CONCAT(c.college)
				ELSE 0
			END as list,
			CASE
				WHEN recruit_type = 'Offer' THEN GROUP_CONCAT(c.college)
				ELSE 0
			END as offers			
		
			FROM a_players p
			
			LEFT JOIN a_schools s 
				ON p.schoolID = s.id
			LEFT JOIN a_players_offers po	
				ON p.id = po.playerID
			LEFT JOIN a_colleges c
				ON po.collegeID = c.id	
			WHERE CONCAT(nameFirst,' ', nameLast) = '".$tag."'
			";

 

Here is the part of the profile that produces the offers or list...  (I added $line['recruit_type'] so I could see which alias it was using.)

 

		

				// Turn CSV into unordered list
				$offersList = $line['offers'];
				$offersList = explode(",",$offersList);
				asort($offersList);
				foreach ($offersList as $offers) {
				echo '<tr><td>' . $offers.$line['recruit_type'];
					
				echo '</td></tr>';	
				}
			echo '</table></div>';					
		

 

Off them for print up as 'college-name'Offer.   (Keep in mind, as I have changed over to a separate Offers table, I haven't redone all the output yet.)

 

There should be 14 that say Offer after them and two that say List.  Marquette and the second instance of Butler should have List after their name.   Actually, I would think the way output is set up, it wouldn't list either of the List schools, just the 14 with Offer.  

 

(I want to create the separate aliases because once a player narrows down to his final list, that takes precedence over all of his offers.  Most kids will have 5-6 before it's over, then reduce to two or three.  In this kid's case, he has 14 offers, so far.  He'll add more this summer, then come out with a list of 4-5 in the fall.)

 

Edited May 8, 2020 by Jim R

[Jim R]

I'm pretty sure I had the wrong usage of GROUP_CONCAT.  Here is the current query...

SELECT *,p.id as pid,
		
			GROUP_CONCAT(
				CASE
					WHEN recruit_type = 'List' THEN c.college
					ELSE 0
				END) as list,	
			GROUP_CONCAT(
				CASE
					WHEN recruit_type = 'Offer' THEN c.college
					ELSE 0
				END) as offers
			
			FROM a_players_offers po

			LEFT JOIN a_colleges c
				ON po.collegeID = c.id	
			
			LEFT JOIN a_players p
				ON p.id = po.playerID
			LEFT JOIN a_schools s 
				ON p.schoolID = s.id	
			
			WHERE CONCAT(nameFirst,' ', nameLast) = '".$tag."'

 

It's getting closer in terms of output...well...I think.  The two which should be List no longer list the school as an offer.  It's still not right, but it might be on the output side now.  

 

 

 

[Jim R]

I printed             echo $line['list'];

It produced...

0,0,0,0,0,0,0,0,0,0,0,0,0,0,Butler,Marquette

There are 16 total rows with this player, 14 offers and 2 listed.  I'm not sure why it would be 14 zeros.  It should just produce Butler and Marquette because only two rows have List as recruit_type.  

Edited May 8, 2020 by Jim R

[Jim R]

Getting closer:

 

			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,	

			GROUP_CONCAT(
				CASE
					WHEN recruit_type = 'Commit' THEN c.college					
				END) as commit				

 

Didn't know I didn't have an 'else' in the Case statements. 

Edited May 9, 2020 by Jim R