CODEWITHSUNDEEP
mysql
Seef Le Roux
Seef Le Roux

Reputation: 15

Left Join with temporary RANK() column

I am trying to LEFT JOIN a column where the values are calculated using rank() with a lookup column from another table (Points).

The query looks like this:

SELECT *, id, Entry.Fin_place, Athlete.First_name, Athlete.Last_name, 
      Fin_Time, Points.Fin_points, Athlete.Ath_no,
      Athlete.Team_no,Team.Team_no,Team.Team_abbr, 
      DENSE_RANK() OVER ( ORDER BY `Fin_Time` DESC) AS rankz 
FROM `Entry` 
LEFT JOIN Athlete ON Entry.Ath_no = Athlete.Ath_no
LEFT JOIN Team ON Athlete.Team_no = Team.Team_no
LEFT JOIN Points ON rankz = Points.Fin_place;

MySQL said: #1054 - Unknown column 'rankz' in 'on clause'

The temporary Rank() value must to be used to look up and display the Fin_points from the Points table, but I keep getting the error message.

Some more info for context: The Entry table: enter image description here The blank column is where the Rankz value must be used to lookup the value of Fin_points in the Points table

enter image description here

Upvotes: 0

Views: 127

Answers (1)

P.Salmon
P.Salmon

Reputation: 17615

Qualifying column names with the table or table alias is helpful when posting so we can see where the code could be split. ASSUMING fin_time exists in one of the first 3 invoked tables you could park that lump of code in a cte and join points eg

with cte as
(
SELECT *, id, Entry.Fin_place, Athlete.First_name, Athlete.Last_name, 
      Fin_Time, #Points.Fin_points, 
      Athlete.Ath_no,
      Athlete.Team_no,Team.Team_no,Team.Team_abbr, 
      DENSE_RANK() OVER ( ORDER BY `Fin_Time` DESC) AS rankz 
FROM `Entry` 
LEFT JOIN Athlete ON Entry.Ath_no = Athlete.Ath_no
LEFT JOIN Team ON Athlete.Team_no = Team.Team_no
)
select * , Points.Fin_points
from cte
LEFT JOIN Points ON rankz = Points.Fin_place;

The reason your code errors is because rankz does not exists at the time of the join.

Upvotes: 1

Related Questions