Can't get the logic right.

[Nouri31]

Hi, I have been working on this problem all day and I cant get the logic right. we are asked "Given a specific taxi, list its history – drivers, accidents, mileage "



The SQL that I am using for this query is shown below:

SELECT Driver.[Driver ID], Rental.[Mile Out], Taxi.[Taxi ID], Taxi.Manufacturer, Taxi.[Date of Purchase], Taxi.[Accident Occurence]
FROM Taxi INNER JOIN (Driver INNER JOIN Rental ON Driver.[Driver ID] = Rental.[Driver ID]) ON Taxi.[Taxi ID] = Rental.[Taxi ID]
WHERE (((Taxi.[Taxi ID])=[Enter Taxi ID Number]));

I'm pulling the data from 3 different tables, this query works when the taxi has been rented out, however when taxi has no Rental ID the query doesnt show any other informations such as Date of Purchase or Manufacturer. I am told that the problem is "Taxi INNER JOIN (Driver INNER JOIN Rental ON Driver.[Driver ID] = Rental.[Driver ID]) ON Taxi.[Taxi ID] = Rental.[Taxi ID]" statement.

What i want to know is how to pull data based on information needed even though the taxi has not been rented by anyone else. Any help would be appreciated.

[jzwp11]

You will need 2 queries to do this. First create a query that joins the rental and driver tables; something like this:


query name: qryDriverRentalInfo
SELECT Rental.[Mile Out], Rental.[Driver ID], Rental.[Taxi ID]
FROM Driver INNER JOIN Rental ON Driver.[Driver ID] = Rental.[Driver ID];

Now create another query that joins the taxi table to the above query, you will need to alter the join type to include all records from the Taxi table and those records from the above query that match this would be a LEFT JOIN

SELECT Taxi.[Taxi ID], Taxi.Manufacturer, Taxi.[Accident Occurence], Taxi.[Date of Purchase], qryDriverRentalInfo.[Mile Out], qryDriverRentalInfo.[Driver ID]
FROM Taxi LEFT JOIN qryDriverRentalInfo ON Taxi.[Taxi ID] = qryDriverRentalInfo.[Taxi ID];

[Nouri31]

Hi jzwp11,

thank you so much. worked like a charm.

[jzwp11]

You're welcome; glad we could help.