Soumyajit Das Soumyajit Das - 6 months ago 27
SQL Question

Derived table in mysql showing error

When I am using this derived table in my query in MYSQL it always pops up with the error

'' UNKNOWN COLUMN COMPANY.CNAME''
though in the database there is
CNAME
field present in COMPANY table:

Error:

error

Diagram:

er diagram

Answer

Your error appears at first line. Try it by changing company.cname to cname or t.cname

select cname from
(
   select count(medication.id) as total, company.cname 
   from medication
   join company on medication.company_id = company.id
   group by company.cname
   order by count(medication.id) desc
   limit 1
) as t
Comments