Jump to content

Complicated (for me) Order By issue...


Jim R

Recommended Posts

I didn't see p.grade wasn't in there, but I was looking for mostRecent.grade.  

I put it in, bottom of the sub select.  Same error shows up.  

 

Unknown column 'mostRecent.grade' in 'field list'

$query = "SELECT *,mostRecent.grade,
		mostRecent.nameLast
		
		FROM (SELECT p.id as pid,
		b.playerID as bplayerID,
		b.userID as buserID,
		s.toggle AS stoggle,
		o.toggle AS otoggle,
		p.city as pcity,
		p.school as pschool,
		s.city as scity,
		s.school as sschool,
		r.id as rid,
		r.opp_city as roppcity,
		r.opp_school as roppschool,
		o.city as ocity,
		o.school as oschool,
		r.city as rcity,
		r.school as rschool,
		r.nameFirst as rfirst,
		r.nameLast as rlast,
		p.nameFirst as pfirst,
		p.nameLast as plast,
		p.grade as pgrade
		
		
		FROM a_players_reviews r 
		
		LEFT JOIN a_players p 
			ON CONCAT (r.nameFirst,r.nameLast) = CONCAT (p.nameFirst,p.nameLast)
	
		LEFT JOIN a_schools s 
			ON CONCAT(r.city,r.school) = CONCAT(s.city,s.school)
		LEFT JOIN a_schools o	
		 	ON CONCAT(r.opp_city,r.opp_school) = CONCAT(o.city,o.school)
		
		LEFT JOIN a_player_bookmark b
			ON p.id = b.playerID && '". $userID ."' = b.userID
		
		WHERE bookmark>0
		ORDER BY r.id desc
		LIMIT 15) mostRecent
 			ORDER BY mostRecent.grade,mostRecent.nameLast	
		";

 

Link to comment
Share on other sites

Removed the alias from p.nameLast, and now the errors are gone.  Still not fully there, as some of the content isn't being printed.  It's not longer recognizing the player's name, his school and the opponent.  I've added columns to the sub select, and I've used the aliases.  

The name alias rfirst and rlast aren't showing.  

Edited by Jim R
Link to comment
Share on other sites

Well...I removed all the duplicate columns and replaced them IDs, and while it took some trial and error to reconfigure the joins and output, I was able to get it to work.  

Below is the final query.  I'm glad it was at least something I hadn't done before and just couldn't get it figured out.  

 

Thank you for your time.  

	$query = "SELECT *,mr.nameFirst,mr.grade,
		mr.nameLast
		
		FROM (SELECT p.id as pid,
		b.playerID as bplayerID,
		b.userID as buserID,
		s.toggle AS stoggle,
		o.toggle AS otoggle,
		p.city as pcity,
		p.school as pschool,
		s.city as scity,
		s.school as sschool,
		r.id as rid,
		o.city as ocity,
		o.school as oschool,
		oppID,
		nameFirst,
		nameLast,
		feet,
		inches,
		position,
		grade,
		time,
		event,
		review
		
		
		FROM a_players_reviews r 
		
		LEFT JOIN a_players p 
			ON p.id = r.playerID
	
		LEFT JOIN a_schools s 
			ON p.schoolID = s.id 
		LEFT JOIN a_schools o	
		 	ON r.oppID = o.id 
		
		LEFT JOIN a_player_bookmark b
			ON p.id = b.playerID && '". $userID ."' = b.userID
		
		WHERE bookmark>0
		ORDER BY r.id desc
		LIMIT 15) mr
 			ORDER BY mr.grade,mr.nameLast
		";

 

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

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.