BrightHalo BrightHalo - 2 months ago 6
SQL Question

Oracle SQL Cases, Comparing Columns in Different Tables and Joins

Instructions:

Select columns EName and VehicleName. If EName Doesn't own a vehicle display
Null as VehicleName.


Relevant SQL tables and columns:

Table | Coulumns
--------------------------
Emp | EmpNo, EName
Vehicle | VehicleId, VehicleName
EmpVehicle | EmpNo, VehicleId


My current attempt is:

SELECT Emp.EName, Vehicle.VehicleName
FROM Emp
INNER JOIN EmpVehicle
ON Emp.EmpNo = EmpVehicle.EmpNo
Inner JOIN Vehicle
ON EmpVehicle.VehicleId = Vehicle.VehicleId


The above query works in the sense that it displays all employee names with a EmpVehicle.VehicleId and Vehicle.VehicleName entry but I have no clue how to include employee names that don't have a vehicle with joins involved. I have been able to determine that not all EmpNo values in the Emp.EmpNo column are in the EmpVehicle.EmpNo column and that my above query only displays the names of employees whose employee number are in the EmpVehicle.EmpNo column.

How would you write a case that looks at if an employee number in column Emp.EmpNo is not in column EmpVehicle.EmpNo and if it is not return Null in the displayed VehicleName?

Answer

You should LEFT JOIN to keep data from your Emp table that doesn't match on your INNER JOIN:

SELECT Emp.EName, Vehicle.VehicleName
FROM Emp
LEFT JOIN EmpVehicle
ON Emp.EmpNo = EmpVehicle.EmpNo
LEFT JOIN Vehicle
ON EmpVehicle.VehicleId = Vehicle.VehicleId
Comments