Mysql – SQL query does not recognise derived table

derived-tablesMySQLmysql-8.0

So I've made a simple SQL query:

SELECT companyname
FROM works w1, (
                SELECT companyname,sum(salary) AS sumsal
                FROM works
                GROUP BY companyname
               ) w2
WHERE
w1.companyname=w2.companyname

it works fine so far but then I add the following line in the where clause:

AND w2.sumsal=(SELECT MIN(w2.sumsal)
               FROM w2);

I get:

ERROR 1146 (42S02): Table 'companydb.w2' doesn't exist

I'm using MySQL 8.0.

Best Answer

Before answering the question, you are using WHERE instead of JOIN for joining the tables, and join the tables using the company name instead of the row ID / primary key. It's better use JOIN with row ID when joining tables (if your are using the company name as the primary key you really have to add to add a row ID and use it instead as the key).

Regarding your question, the problem is that the subquery w2 can't be used in the right part of the SELECT because is out of scope, but you can use a Common Table Expression to create a "temporary view" that can be used in any part of the query. Then you can use two methods for getting the result:

Using MIN():

with sums as (select companyname, sum(salary) AS sumsal
  from works
  group by companyname
)
select *
from sums
where sumsal=(select min(sumsal) from sums)

Using EXISTS:

with sums as (select companyname, sum(salary) AS sumsal
  from works
  group by companyname
)
select *
from sums
where not exists (select * from sums s2 where s2.sumsal<sums.sumsal)

I have created this db<>fiddle for testing it online, there I have used JOIN with the company Id instead of the name as an example of how you can use that.

Related Question