SQL inner join with max

Question

I have an exercise on queries and I can't seem to find the solution to one of them. I have two tables : Aircrafts and Certified.

In the table Aircrafts I have the information AID (ID of the plane), Aname (Name of the plane) and Crusingrange(Max distance the plane have) :

AID    Aname       Crusingrange            
1     BoeingFr        25000   
2     BoeingUS        50000   
3      Jet01          3000  
4      Jet02          4000 

In the table CERTIFIED I have this information AID (ID of the plane) and EID (ID of the pilot) :

AID    EID                   
1      199     
2      199  
1      110         
3      110        
3      109  
4      109    

What I want is the ID of the pilot and the ID of the plane with the greatest cruising range he/she can fly.

EID    AID                   
199     2    
110     1  
109     4 

I know I have to use MAX within INNER JOIN but I really don't find the solution and I have tried to break down my code but still impossible.

Thank you

Answer 1

• Firstly, get maximum value of Crusingrange for a EID, using Join and Group By. Use this result as a Derived table.
• Now, again Join back to the main tables using maximum value of Crusingrange and EID.

For MySQL, try the following:

SELECT c1.EID, 
       a1.AID
FROM CERTIFIED AS c1
JOIN Aircrafts AS a1 ON a1.AID = c1.AID 
JOIN        
(
  SELECT c.EID, 
         MAX(a.Crusingrange) AS Crusingrange 
  FROM CERTIFIED AS c
  JOIN Aircrafts AS a ON a.AID = c.AID 
  GROUP BY c.EID 
) AS dt ON dt.Crusingrange = a1.Crusingrange AND 
           dt.EID = c1.EID