Hi all. I have a request to remove membership numbers that haven't had requests run against them and have incorrect numbers for their account numbers. Therefore i've come up with this SELECT CU.CustomerID , CU.MembershipNumber , SR.ServiceRequestID FROM dbo.Customer CU LEFT JOIN dbo.ServiceRequest SR ON CU.CustomerID = SR.CustomerID WHERE CU.CompanyID = 1176 AND SR.ServiceRequestID IS NULL AND SUBSTRING(CU.MembershipNumber, 20, 1) != '9' What i started off doing was this as it made more sense to me SELECT CU.CustomerID , CU.MembershipNumber FROM dbo.Customer CU WHERE NOT EXISTS ( SELECT CustomerID FROM dbo.ServiceRequest SR WHERE CU.CustomerID = SR.CustomerID AND CU.CompanyID = 1176 AND SUBSTRING(CU.MembershipNumber, 20, 1) != '9' ) These two queries should do the same thing shouldn't they??? for the life of me i can't get the same results in the second query! What am i doing wrong?
Yes, the two queries are different. I guess the fault is in the first query i.e. SELECT dbo.Customer.CustomerID, MembershipNumber, ServiceRequestID FROM dbo.Customer LEFT JOIN dbo.ServiceRequest ON dbo.Customer.CustomerID = dbo.ServiceRequest.CustomerID WHERE CompanyID = 1176 AND ServiceRequestID IS NULL AND SUBSTRING(MembershipNumber,20,1) != '9' You are doing the LEFT JOIN but when you used the columns of dbo.ServiceRequest, that will be converted to an inner join. Hence ServiceRequestID IS NULL becomes a filter on the ServiceRequest table. If you would add the above clause in the NOT EXISTS query, I believe the results would become the same. PS: You should have added the DDL and some test data EDIT: If CompanyID and MembershipNumber columns are part of the Customer table then your query should be like the following SELECT customerid, membershipnumber WHERE NOT EXISTS (SELECT CustomerID FROM dbo.ServiceRequest SR WHERE CU.CustomerID = SR.CustomerID ) AND CompanyID = 1176 AND SUBSTRING(MembershipNumber, 20,1) != '9' The CompanyID and MembershipNumber filters should not be part of the NOT EXISTS statement ;)