CODEWITHSUNDEEP
sqlsql-servert-sqljoin
Muhammad Taqi
Muhammad Taqi

Reputation: 5424

MS SQL Merge two queries to get One Result

I am implementing a vehicle management system. I have two different queries, one for fetching all vehicles of an agency and one for fetching drivers. Every vehicle is has a driver assigned to it at a time. Now I want to merge these two queries to returns vehicles details along with details of the driver assigned to the vehicle.

1st query to get drivers and their names:

SELECT UserInfo.Name
FROM DriverInfo INNER JOIN UserInfo ON DriverInfo.Email = UserInfo.Email 
    INNER JOIN Agency ON UserInfo.AgencyID = Agency.AgencyID 
WHERE (UserInfo.AgencyID = 1) 
ORDER BY DriverInfo.DriverId DESC

2nd query to get vehicles details:

SELECT Vehicle.VehicleRegNum AS [Registration Number], 
    Vehicle.EngineNum AS [Engine Number], 
    Vehicle.FileRef AS [File Refrence], 
    Type.Name AS Type, 
    Make.Name AS Make ,
    Vehicle.DriverID AS DriverID
FROM Vehicle INNER JOIN Type ON Vehicle.Type = Type.TypeId 
    INNER JOIN Make ON Vehicle.Make = Make.MakeId 
WHERE (Vehicle.Approve = 1) 
    AND (Vehicle.AgencyId = 1) 
    AND (Vehicle.Maintained = 1) 
ORDER BY Vehicle.VId DESC

In this query, I am getting the DriverID and in the DriverInfo:

DriverId, LicenseType, AppointmentDate, LicenseExpiryDate, ContractExpiryDate, Email

Upvotes: 0

Views: 69

Answers (2)

NickyvV
NickyvV

Reputation: 1744

I believe the following will work, depending on your requirements you might wanna change the JOIN type of DriverInfo:

SELECT Vehicle.VehicleRegNum AS [Registration Number]
    ,Vehicle.EngineNum AS [Engine Number]
    ,Vehicle.FileRef AS [File Refrence]
    ,Type.NAME AS Type
    ,Make.NAME AS Make
    ,UserInfo.NAME AS Driver
FROM Vehicle
INNER JOIN Type ON Vehicle.Type = Type.TypeId
INNER JOIN Make ON Vehicle.Make = Make.MakeId
LEFT JOIN DriverInfo ON DriverInfo.DriverID = Vehicle.DriverID
LEFT JOIN UserInfo ON DriverInfo.Email = UserInfo.Email
    AND (UserInfo.AgencyID = 1)
--Agency is never used, so don't join?
--LEFT JOIN Agency ON UserInfo.AgencyID = Agency.AgencyID
WHERE (Vehicle.Approve = 1)
    AND (Vehicle.AgencyId = 1)
    AND (Vehicle.Maintained = 1)
ORDER BY Vehicle.VId DESC

Upvotes: 1

TTeeple
TTeeple

Reputation: 2989

Just add on the additional tables in your join.

SELECT Vehicle.VehicleRegNum AS [Registration Number], 
Vehicle.EngineNum AS [Engine Number], 
Vehicle.FileRef AS [File Refrence], 
Type.Name AS Type, 
Make.Name AS Make,
DriverInfo.DriverId,
DriverInfo.LicenseType,
DriverInfo.AppointmentDate,
DriverInfo.LicenseExpiryDate,
DriverInfo.ContractExpiryDate,
DriverInfo.Email
FROM Vehicle 
INNER JOIN Type 
    ON Vehicle.Type = Type.TypeId 
INNER JOIN Make 
    ON Vehicle.Make = Make.MakeId 
INNER JOIN DriverInfo 
    ON Vehicle.DriverID = DriverInfo.DriverId
INNER JOIN UserInfo 
    ON DriverInfo.Email = UserInfo.Email 
INNER JOIN Agency 
    ON UserInfo.AgencyID = Agency.AgencyID 
    AND Vehicle.AgencyID = Agency.AgencyID
WHERE (Vehicle.Approve = 1) 
AND (Vehicle.AgencyId = 1) 
AND (Vehicle.Maintained = 1) 
ORDER BY Vehicle.VId DESC

Upvotes: 1

Related Questions