In MySQL 4.0, is it possible to find all records that don't exist in one query. I managed to do it in Access by referencing a query that I had previously created, but I was wondering if it was possible to do this in one step.
SELECT C.CustomerID, ([JobStatus]="0" And [JobTypeID]=2) AS KountNA, Count([KountNA]) AS KountComplete FROM Workorders AS W INNER JOIN Customers AS C ON W.CustomerID = C.CustomerID GROUP BY C.CustomerID, ([JobStatus]="0" And [JobTypeID]=2) HAVING (((([JobStatus]="0" And [JobTypeID]=2))=-1)); Would show all Customers that have JobTypeID=2 and JobStatus="0". I realise that the KountComplete expression is not required. I then used a RIGHT JOIN to filter out all the records from the Customers table not in this query, as below: SELECT C.CustomerID, QC.KountComplete, C.NameNumb, C.BillingAddress, C.City, C.PostalCode FROM qryCountCompleteServices AS QC RIGHT JOIN Customers AS C ON QC.CustomerID = C.CustomerID WHERE (QC.KountNA) Is Null); So, can I do that without using a sub-query in MySQL 4.0? Thanks Andrew -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.10.4 - Release Date: 27/04/2005
