CODEWITHSUNDEEP
mysqldatabase
Rozen
Rozen

Reputation: 139

Cant find the issue that causes error of mysql statement

So for this specific mysql that I have, I try to join the 2 tables together then pick out the names that have max values in another column in the joined table. I suspect the issue is where I connect 2 statements together.

select ds_name
from result 
where result.ds_sectionnumber = (select max(ds_sectionnumber) from result) 
from (select department.Dcode as ds_code, department.Dname as ds_name, section.Sectionnumber as ds_sectionnumber 
from department join section on department.Dcode = section.Dcode) as result;

Upvotes: 2

Views: 51

Answers (1)

Code Lღver
Code Lღver

Reputation: 15603

Your query should be this:

select ds_name
  FROM (select department.Dcode as ds_code,
               department.Dname as ds_name, 
               section.Sectionnumber as ds_sectionnumber 
        FROM department 
        JOIN section on department.Dcode = section.Dcode) as result
  WHERE ds_sectionnumber = (
            select max(ds_sectionnumber) FROM 
                  (select department.Dcode as ds_code,
                     department.Dname as ds_name, 
                    section.Sectionnumber as ds_sectionnumber 
                       FROM department 
                       JOIN section 
                      ON department.Dcode = section.Dcode) as result);

Upvotes: 3

Related Questions