Jim R

I am. I have a school table and coach table. In the coach table, I have school_id. Each time there is a coaching change, I add a new row and fill in the end season of the coach he replaces. I've read some things saying I can have a subquery in a view. Maybe it was because the subquery was in the From statement. MySQL program won't allow it, and there is something about MySQL workbench that I don't like. I don't remember at the moment though, but I've played around with it. I'm using Sequel Pro.

...and my host isn't upgrading to 5.7 anytime soon evidently. So I'm looking for an alternative way to query the most recent instances of a each result. I have the subquery that works for my site, but I'm wanting to create a View since I've eliminated all duplicated data 😉 Here is the query that creates the list pictured below: select s.id as sid,coachFirst,coachLast,season_hired,username,s.server,toggle,city,school,nickname,bsect from a_coach c inner join a_schools s on s.id = c.school_id order by s.id,season_hired desc sid = school ID sids 1, 4, 7 and 10 all made coaching changes this past summer. I want to reflect them as the current coach for the school (sid) based on the season_hired column. Group By, of course, shows the first instance, which really seems counterintuitive. I have messed around with using MAX, e.g. max(season_hired): max(season_hired) in the SELECT statement with a GROUP BY s.id after the JOIN. but it shows the past coach with the current hire date, so it would show for sid(1) = Rodney Williams hired in 2021. Is my only option the nested subquery? Here is the query with the subquery that gets me what I want for my website, but you can't create views with subqueries in MySQL 5.6. select * FROM ( select s.id as sid,coachFirst,coachLast,season_hired,username,s.server,toggle,city,school,nickname,bsect from a_coach c left join a_schools s on s.id = c.school_id order by sid,season_hired desc) mr group by mr.sid

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.

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.

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.

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.)

I've restructured all the tables.

It's not a problem, just noting the irony of the situation. Still surprised there isn't a statement to get acronyms, or at least some way to use substring, noting a delimiter. Not anymore. All IDs now.

Between LEFT, RIGHT, SUBSTRING, SUBSR and a couple of more, there seems to be 4-6 ways I could just get the first letter of each field. None to get the first letter of each word? Code gymnastics? Anymore than using CASE? Normally I'm hammered on here for having too many columns or too many times duplicating data.

Hard to believe there isn't a way to get what is essentially an acronym.

It would just be the space in between the two words. Is the syntax close on what I typed in my OP?

I have a column = city In some instances, I just need the first the letter of each word in the city. Some city names are more than one word. So Flora would be (F) and Fort Wayne (FW). It's the WHEN toggle = 2 ... line. $query="SELECT id, (CASE WHEN toggle = 1 THEN concat(city,' ',school) WHEN toggle = 2 THEN concat(school,' (',substring(city,1,1),')') ELSE school END) as schools FROM a_schools ORDER BY schools "; Instead of substring i've tried acronym and initials. acronym(city) I found those in a search, but I got errors suggesting they weren't valid options. I've also tried regex_replace(city, [a-z], '') but got syntax errors too. Could be I didn't apply it right.

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

It's not printing any of my name variables or the school and city of the player. It's printing the opponent now. So it's not printing the ones where I have duplicate entries.

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.

Removed the pgrade alias, now I'm getting Unknown column 'mostRecent.nameLast' in 'field list'

You said to alias them if needed.

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

I tried mostRecent.grade and mostRecent.nameLast in the inner select and outer select. I tried them in both at the same time. All three instances I got the following error: 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 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 ";

I pasted your original query. It yielded the same set of errors we're getting now, and the order by columns at the end were not in the original select list, certainly not with mostrecent.grade and mostrecent.nameLast. Giving it a try.

At least we're working down the query. 🥴

Now I'm getting... Duplicate column name 'id' $query = "SELECT * 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 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 ";

Getting this error: Unknown column 'rfirst' in 'on clause' $query = "SELECT * 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 FROM a_players_reviews r LEFT JOIN a_players p ON CONCAT (rfirst,rlast) = CONCAT (pfirst,plast) LEFT JOIN a_schools s ON CONCAT(rcity,rschool) = CONCAT(scity,sschool) LEFT JOIN a_schools o ON CONCAT(roppcity,roppschool) = CONCAT(ocity,oschool) LEFT JOIN a_player_bookmark b ON pid = bplayerID && '". $userID ."' = buserID WHERE bookmark>0 ORDER BY rid desc LIMIT 15) mostRecent ORDER BY mostRecent.grade,mostRecent.nameLast ";

But that gets me back to the duplicate column error. I only messed around with the aliases because of that. So what you modified sent the duplicate column error.

I've been searching for other samples on the Googles and play around with pid / p.id, I removed it from the SELECT, and the error moved to the next p. instance, 'p.city', saying it was a duplicate column name. Removed all the p. columns, and it turned to having a duplicate city column. I removed all the column names in the sub select, leaving just * , and I was back to having duplicate column 'id'. Is it not seeing any of the aliases?