Evidently can't create a view with a subquery in MySQL 5.6...

[Jim R]

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

 

[requinix]

Actually you can create a view with a subquery. But you shouldn't for this. Relying on GROUP BY as a way to choose distinct rows is a MySQL quirk.

So you're storing coach information alongside the school? What happens when the coach changes? Do you duplicate all the other school information into a new row? Why aren't you storing school information in one place and school coaches in another?

[Barand]

If you had used a data model like the one I suggested to you in May 2019 ...

... the coached_from and coached_until dates would give you who the coach was/is at any point in time.

[Jim R]

3 hours ago, requinix said:

Actually you can create a view with a subquery. But you shouldn't for this. Relying on GROUP BY as a way to choose distinct rows is a MySQL quirk.

So you're storing coach information alongside the school? What happens when the coach changes? Do you duplicate all the other school information into a new row? Why aren't you storing school information in one place and school coaches in another?

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.  

[Jim R]

3 hours ago, Barand said:

If you had used a data model like the one I suggested to you in May 2019 ...

... the coached_from and coached_until dates would give you who the coach was/is at any point in time.

OK...I'll play around with it, and I see what you're saying.  I'lll have to set up the coaching tenure table before I can visualize how it will really help me with queries, but as always, I appreciate your input.  

When you suggested that, I wasn't ready to start adding coaches, and this is the first off-season I've messed with coaching changes.  Everything is working well except this view, but I'd like to get the view set up for my writers.  

 

There is one issue with this though.  When there is a coaching change, there is a change of contact information.  I have the server name of each school in the school table, which for the most part is static (some use a personal email address instead of a school address).  I have the Username (before the @) in the coach table.  So even if using this extra table, I will have to type a new user name on each change.  So either I change in the coach table  with each change, or add it with a new coaching tenure line.  

 

Edited August 20, 2020 by Jim R

[Jim R]

I also have a seasons table.  I could join that for the match, right?

[Jim R]

Even simpler as it turns out.  This returns every coach whose tenure hasn't ended yet.  

 

select s.id as sid,coachFirst,coachLast,season_hired,season_end,username,s.server,toggle,city,school,nickname,bsect from a_coach c
left join a_schools s
	on s.id = c.school_id
where season_end is null		
order by s.id