With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.
Course Of The Month
3 days, 4 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
How to search a table based on a seperate list of names.
Posted on 2011-03-21
I need to search a Microsoft SQL 2000 database table called Candidates for email addresses for a seperate list of names I was given.
My list has columns for First Name, Last Name, Title, Company, Address, City, State, Zip, etc. The Candidate table has most of these fields and many others. I believe the Company and Address information is actually in a Companies table, linked to the Candidates table.
So my first question is how do I search the table based on information from my list?
Then, I get really confused thinking of how to make sure I'm pulling the correct information. All I need is the email address for each name on my list. But the database is huge, so how do I make sure I have the correct person? I can't be sure the company name or address information will be spelled or written exactly in the database table as it is in my list.
Any help with even part of this would be greatly appreciated. If it makes it easier or better for anyone, I can breake the question into multiple questions.
Thanks!
My list has columns for First Name, Last Name, Title, Company, Address, City, State, Zip, etc. The Candidate table has most of these fields and many others. I believe the Company and Address information is actually in a Companies table, linked to the Candidates table.
So my first question is how do I search the table based on information from my list?
Then, I get really confused thinking of how to make sure I'm pulling the correct information. All I need is the email address for each name on my list. But the database is huge, so how do I make sure I have the correct person? I can't be sure the company name or address information will be spelled or written exactly in the database table as it is in my list.
Any help with even part of this would be greatly appreciated. If it makes it easier or better for anyone, I can breake the question into multiple questions.
Thanks!
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
-
4
2
- +1
8 Comments
Now you know why using "Natural Keys" such as First Name and Last Name (or even Company Name) is a bad idea. ;-)
The best you can hope for is to get as many as possible on your first pass and then to refine your results using additional approaches. For the first pass, match as many columns as possible (which will probably mean that you will need to do some JOINs to get the data from the database). I would then start matching fewer columns, e.g. omit the First Name column and match the rest. (My reasoning would be Richard doesn't match Dick, Rick, or Ricky but the last name should be the same if it is the same person.)
An alternative is to go with a SoundEx search on the names . . . but that can lead to false positives when Smith, Smithe, and Smythe get matched.
The best you can hope for is to get as many as possible on your first pass and then to refine your results using additional approaches. For the first pass, match as many columns as possible (which will probably mean that you will need to do some JOINs to get the data from the database). I would then start matching fewer columns, e.g. omit the First Name column and match the rest. (My reasoning would be Richard doesn't match Dick, Rick, or Ricky but the last name should be the same if it is the same person.)
An alternative is to go with a SoundEx search on the names . . . but that can lead to false positives when Smith, Smithe, and Smythe get matched.
8080_Diver you are way over my head. :-)
Sharath, sample data is basic. The table contains fields like FirstName LastName Email, and my Excel list contains fields like FirstName, LastName, etc. I need the Email from the table based on the names on my Excel sheet. I don't know how to query the database based on information on my list. I don't want to type in each name individually into the code.
Sharath, sample data is basic. The table contains fields like FirstName LastName Email, and my Excel list contains fields like FirstName, LastName, etc. I need the Email from the table based on the names on my Excel sheet. I don't know how to query the database based on information on my list. I don't want to type in each name individually into the code.
In your Excel Spreadsheet, you can generate a serries of INSERT statements
You can copy/paste these INSERTS string into your Query Analyser and insert it into a temporary table.
You can then use the temporary table and JOIN with another table you wanted to search to.
Let me know I need to expound these.
You can copy/paste these INSERTS string into your Query Analyser and insert it into a temporary table.
You can then use the temporary table and JOIN with another table you wanted to search to.
Let me know I need to expound these.
vandalesm, thanks, that makes sense but I do need a little more detail. Woul I create an INSERT statement for eah of the couple thousand names on my list? Not a real big deal since I can auto fill in Excel. I've never worked with a temp table. I assume they're created the same as a regular table?
I was thinking along the lines of a split function I heard of once. Something that would loop through the table for each name on my list and return the email address. Say from a csv list which is easily created from my Excel.
I was thinking along the lines of a split function I heard of once. Something that would loop through the table for each name on my list and return the email address. Say from a csv list which is easily created from my Excel.
--Create a temporary table
CREATE TABLE #names (first_name varchar(30), last_name varchar(30))
-- Then copy/paste your INSERT statement into the temporary table.
-- Search the Candidate table
SELECT c.*
FROM Candidate c
INNER JOIN #names n
ON n.first_name = c.first_name
AND n.last_name = c.last_name
CREATE TABLE #names (first_name varchar(30), last_name varchar(30))
-- Then copy/paste your INSERT statement into the temporary table.
-- Search the Candidate table
SELECT c.*
FROM Candidate c
INNER JOIN #names n
ON n.first_name = c.first_name
AND n.last_name = c.last_name
808_Diver, thank you for pointing out the diffirent iterations of joins to filter the data.
vandalesm, thanks so much. I was looking for a different way of doing this but it turned out that creating a new table with the spreadsheet data and joining it to the database table I need to search was not as time consuming as I thought it would be.
I really appreciate everyone's time and effort.
Have a great day!
vandalesm, thanks so much. I was looking for a different way of doing this but it turned out that creating a new table with the spreadsheet data and joining it to the database table I need to search was not as time consuming as I thought it would be.
I really appreciate everyone's time and effort.
Have a great day!
Featured Post
![[Webinar] Learn How Hackers Steal Your Credentials](https://filedb.experts-exchange.com/files/public/2017/5/21/e0b7778f-e79e-4b5d-9ec6-5496e40be42e.png)
Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
This article explains how to reset the password of the sa account on a Microsoft SQL Server. The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses
Course of the Month3 days, 4 hours left to enroll
695 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.