Why doesn't this LEFT JOIN work?
I am trying to write a query that will show a list of appliances with their installation date and the date of any service that has taken place after the appliance was installed.
As they won't all have a service date, I want to show this using the left join, but when I tried the statement below it works just like an INNER JOIN as far as I can see.
The report should show something like this:
Manuf Date Purch CustomerID Date Serv JobTypeID JobStatus
VOKERA 30/07/2004 619 07/04/2005 2 0
VOKERA 04/05/2004 754
Vokera 23/03/2005 1843
BAXI 05/07/2004 1950 19/11/2004 2 0
VOKERA 24/05/2004 2343 25/11/2004 2 0
VOKERA 28/05/2004 2523 11/11/2004 2 0
VOKERA 20/10/2004 2768 22/03/2005 2 0
instead it only shows records that have a Service date.
My query at the moment looks like this:
SELECT A.Manufacturer, A.DateInstalled, A.CustomerID, w.DateFinished, w.JobTypeID, w.JobStatus
FROM Appliances AS A LEFT JOIN Workorders AS w ON A.CustomerID = w.CustomerID
WHERE (((A.DateInstalled)>#3/31/2004#) AND ((w.DateFinished)>[a].[dateinstalled]) AND ((w.JobTypeID)=2) AND ((w.JobStatus)="0"))
ORDER BY A.CustomerID;