October 7, 2008 at 10:17 am
Hello,
I am sure this is simple but I can't seem to find an easy way to do it. I am trying to run a simple query against a table to check for phone numbers to see whether or not the phone numbers already exist in the table or not. The problem is the phone numbers are in an excel spreadsheet and there are over 700 of them. I'm sure there has to be an easier way to get this info than to go through and add all 700 numbers in the Where clause via the OR operator like below?
SELECT UserToCustomer.Comments, User.UserType, User.Name, User.FirstName, User.LastName, User.AreaCode, User.PhoneNumber
FROM UserToCustomer INNER JOIN User ON UserToCustomer.UserId = User.UserId INNER JOIN Customer ON UserToCustomer.CustomerId = Customer.CustomerId INNER JOIN Dept ON User.UserID = Dept.DeptID
WHERE (Customer.PhoneNumber = '8979642' OR Customer.PhoneNumber = '5463962' OR Customer.PhoneNumber = '5466313' OR Customer.PhoneNumber = '8943542')
Any help would be most appreciated.
Thank you
October 7, 2008 at 11:00 am
You could import them into a temporary table in your database server and then depending on how you decide, if you want to see duplicates or ones without duplicates, you could use an EXISTS or NOT EXISTS.
For Example, If you wanted to see which ones already existed in your database and you imported the 700 rows into a temp table called temp...
SELECT UserToCustomer.Comments,
User.UserType,
User.Name,
User.FirstName,
User.LastName,
User.AreaCode,
User.PhoneNumber
FROM UserToCustomer
INNER JOIN User
ON UserToCustomer.UserId = User.UserId
INNER JOIN Customer
ON UserToCustomer.CustomerId = Customer.CustomerId
INNER JOIN Dept
ON User.UserID = Dept.DeptID
WHERE EXISTS
(SELECT *
FROM temp t
WHERE USER.phoneNumber = t.phonenumber)
If you wanted the opposite you could use Not EXISTS.
October 7, 2008 at 12:49 pm
Worked like a charm! Thanks so much!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply