SQL Maximum value of another table

Question

I am doing a project of the course and I stopped at the last step and I would like a little help from you..

The project is an SQL database with "leasing" (contract for "rental" of cars.)

The tables that matter here and the values are: (there are more values, but I don’t think they matter..)

Tabela 1: Vehicles
PK VehicleID
FK ModelID
FK TypeID
FK ColorID

Tabela 2: Model
PK ModelID
Model

Tabela 3: VehicleType
PK TypeID
VehicleType

Tabela 4: Color
PK ColorID
Color

Tabela 5: Leases
PK LeaseID
FK VehicleID
FK TermID

Tabela 6: LeaseTerms
PK TermID
MaximumKM (maximo de kilometros que o contratante pode andar durante o período.

The problem is that in the project, asks me to make a script that shows a list of vehicles (Vehicleid) with information from Vehicleid, Model(picked up through Modelid), Vehicletype(picked up through Typeid), Color(picked up through Colorid) and the largest Maximumkm with which this vehicle has ever been "rented"..

For example, Customer X rented vehicle 1 with Maximumkm of 100.000km. Customer Y rented vehicle 1 with Maximumkm of 120.000km.

In SELECT, only 1 line can appear for each vehicle and with the maximum value of the Maximumkm that it has already been rented, ie:

Vehicleid = 1, Vehicletype = 'SUV', Model = 'Explorer', Color = 'Blue', Maximumkm = 120,000. The lower value of 100,000 km is ignored.

I am since yesterday researching and trying several Joins without success, because this is the first contact I’m having with SQL! If anyone can help, I’d be grateful.

Answer 1

Raphael the instruction would be the following:

Select VehicleID,  
       MAX(LeaseTerms.MaximumKM) 
from Leases
JOIN LeaseTerms on (Leases.TermID = LeaseTerms.termID) 
GROUP BY VehicleID

In this example I used a table with the following data:

Tabela de Leases

Table of Leaseterms

With this data the result of the query will be:

Now linking the data with the table of Vehicles, see that just add a new JOIN

Select Leases.VehicleID, 
       MAX(LeaseTerms.MaximumKM), 
       Vehicles.Model, 
       Vehicles.Type,
       Vehicles.Model 
from Leases 
JOIN LeaseTerms on (Leases.TermID = LeaseTerms.termID) 
JOIN Vehicles on (Leases.VehicleID = Vehicles.VehicleID)
GROUP BY VehicleID 

Upshot:

Answer 2

In your case you can use a subquery to obtain the desired result:

SELECT v.*,
       (SELECT MAX(lt.MaximumKM)
          FROM Leases l
               INNER JOIN LeaseTerms lt ON lt.TermID = l.TermID
         WHERE l.VehicleID = v.VehicleID)
  FROM Vehicles v