question

jhowe avatar image
jhowe asked

convert query to not exists

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?
sql-server-2008sql-server-2008-r2not-exists
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Is the omission of the alias CU a typo?
0 Likes 0 ·

1 Answer

· Write an Answer
Usman Butt avatar image
Usman Butt answered
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 ;)
7 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

No the first query is correct i am selecting everything from the request table that doesn't have a corresponding record in the customer table i.e. servicerequestID is NULL. It is the second query that i cannot get to match the first.
0 Likes 0 ·
Are CompanyID and MembershipNumber columns of Customer table?
0 Likes 0 ·
Can you please post the DDL of the tables and some test data?
0 Likes 0 ·
If CompanyID and MembershipNumber 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'
0 Likes 0 ·
i've updated the column tables etc. to make it clearer
0 Likes 0 ·
Show more comments

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.