Agfdhyk

I've been having trouble with a SQL subquery, and although I imagine this is fairly basic, the internet does not seem to hold the answer. I have a subquery inside a FROM statement which has a MAX() function within it, and I cannot seem to reference this data in the rest of the query. The query is here:

SELECT 

    m.nameFirst, m.nameLast, t.salary, te.name

FROM

    (SELECT 

         MAX(salary), teamID 

     FROM 

         salaries 

     GROUP BY 

         teamID) AS t, master AS m, teams AS te, salaries AS s

WHERE 

    t.salary = s.salary 

    AND s.teamID = t.teamID 

    AND s.playerID = m.playerID 

    AND te.teamID = t.teamID;

The subquery, when run by itself, returns results which look like this:

+-------------+--------+

| MAX(salary) | teamID |

+-------------+--------+

|    13166667 | ANA    |

|    16000000 | ARI    |...

However, when I try to run the whole query, I get the following result:

ERROR 1054 (42S22): Unknown column 't.salary' in 'field list'

I have tried a few different things, such as t.MAX(salary), MAX(t.salary), and even just t.*, but as I need to use the subquery's results later, it just throws different errors.

What name should I use to call the results of the MAX column of the subquery?

Thanks so much for any help.

you can use an alias for max column and your code will work :

SELECT m.nameFirst, m.nameLast, t.salary, te.name

FROM (

    SELECT MAX(salary) as salary, teamID 

    FROM salaries 

    GROUP BY teamID

) AS t, master AS m, teams AS te, salaries AS s

WHERE t.salary=s.salary AND s.teamID=t.teamID AND s.playerID = m.playerID AND te.teamID=t.teamID;

you can give it an alias to make it easier or in this case accessible

eg

  SELECT MAX(salary) as max_salary, teamID ... 

then later simply reference

  t.max_salary

so in your example change it like this

SELECT m.nameFirst, m.nameLast, t.max_salary, te.name

FROM (

    SELECT MAX(salary) as max_salary, teamID 

 -- rest of query

This is because there is no salary returned in subquery likewise the error name it as max(salary) as some_name then t.some_name=s.salary