I was searching for help to figure out Goals Scored ranking for my hockey database to figure out where a team ranks in the league for the category of Goals Scored. I received some great feedback in the answers here text and had it working great.

Problem is when I went live and uploaded to my web host, they only have MySQL version 5.7 and the window functions only work in 8.0. I have tried other options I saw on this website to rank using variable @rank but ran into issues when I had to JOIN another table of data.

What option do I have to JOIN 2 tables of data and rank the teams in the league based on goals scored?

My initial code that worked using MySQL 8 on XAMPP was

$sql = “SELECT
TeamId,
League Id, GP, G, (G) / (GP) GF, RANK() OVER( PARTITION BY League IdORDER BYGF) Rank FROM team_statsINNER JOINteam_dataONteam_data.TeamId=team_stats.TeamIdWHERELeague Id` = 0;

I also used the following that worked when I ran it on my current web host MySQL version 5.7 but it failed when I put the sql query into my php code because it is reading as 2 different statements.

SET @rank=0; SELECT *, (G) / (GP) GF, @rank:=@rank+1 AS Rank FROM team_stats INNER JOIN team_data ON team_data.TeamId = team_stats.TeamId WHERE League Id = 0 ORDER BY GF DESC;

What would be the best way to join 2 tables and rank the results? Please let me know if I need to add further information as this is all new to me?

I am looking for results that would get me this

Table Results