SQL Maximum value of another table

Navigation
4

I'm doing a course project and I took the last step and would like a little help from you ..

The project is a leased SQL database (contract for "rental" of cars.)

The tables that import here and the values are: (there are more values, but I think they do not 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, it asks me to make a script that shows a list of vehicles (VehicleID) with the information of VehicleID, Model (I get through ModelID), VehicleType (I get through TypeID), Color I get through ColorID) and the largest MaximumKM that this vehicle has ever been rented for.

For example, Customer X rented vehicle 1 with MaximumKM of 100,000Km. Client Y rented vehicle 1 with MaximumKM of 120,000Km.

In the SELECT, only 1 line can appear for each vehicle and with the maximum MaximumMM value it has already rented, that is:

VehicleID = 1, VehicleType = 'SUV', Model = 'Explorer', Color = 'Blue', MaximumKM = 120,000. The lowest value of 100,000Km is ignored.

I've been researching and trying out multiple JOINs since yesterday, because this is the first contact I'm having with SQL! If anyone can help, I'll be very grateful.

    
asked by anonymous 27.04.2018 / 17:25

2 answers

3

In your case you can use subquery to get 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
    
27.04.2018 / 18:08
3

Rafael the statement would be as follows: 

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:

Lease Table

LeaseTermsTable

Withthisdatatheresultofthequerywillbe:

NowlinkingthedatatotheVehiclestable,seethatyoujustneedtoaddanewJOIN

SelectLeases.VehicleID,MAX(LeaseTerms.MaximumKM),Vehicles.Model,Vehicles.Type,Vehicles.ModelfromLeasesJOINLeaseTermson(Leases.TermID=LeaseTerms.termID)JOINVehicleson(Leases.VehicleID=Vehicles.VehicleID)GROUPBYVehicleID

Result:

    
27.04.2018 / 17:41
How to insert caption in graph with two axes and in r? How to make a NOT IN mysql with 2 tables