Clifton Bardwell
asked on
SQL Server 2005 - Comparing Fields' Contents
Let's see if I can explain this adequately...
I have two tables. The first table has a student's full name (first, middle, last). The second table has the name broken into it's parts (one field for FirstName, one for LastName, one for MiddleName). Also in the second table is the StudentID number.
What I need to do is retrieve the StudentID from the second table by comparing the FirstName, MiddleName, and LastName of the second table to to the StudentName in the first table.
Here's an example of what I thought would work...
Running the script above gives me "error at line 8; Incorrect syntax near 't2'"
Any idea what I did wrong and, more importantly, how do I do what I want to do?
I have two tables. The first table has a student's full name (first, middle, last). The second table has the name broken into it's parts (one field for FirstName, one for LastName, one for MiddleName). Also in the second table is the StudentID number.
What I need to do is retrieve the StudentID from the second table by comparing the FirstName, MiddleName, and LastName of the second table to to the StudentName in the first table.
Here's an example of what I thought would work...
SELECT
t1.StudentName,
( SELECT
t2.StudentID
FROM
Table2 t2
WHERE
CONTAINS(t1.StudentName, t2.FirstName)
AND
CONTAINS(t1.StudentName, t2.MiddleName)
AND
CONTAINS(t1.StudentName, t2.LastName)
) AS StudentID
FROM
Table1 t1
Running the script above gives me "error at line 8; Incorrect syntax near 't2'"
Any idea what I did wrong and, more importantly, how do I do what I want to do?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
CONTAINS should be used only if you have the Full Text Search service installed and running.
Using LIKE operator it can bring false positives (names that are like but then aren't what you really want). For an exact match use this query:
Using LIKE operator it can bring false positives (names that are like but then aren't what you really want). For an exact match use this query:
SELECT t2.StudentID, t1.StudentName
FROM Table1 t1
INNER JOIN Table2 t2 ON t1.StudentName = t2.FirstName + ' ' + t2.MiddleName + ' ' + t2.LastName
Try these-
Hope it helps!
SELECT
*
FROM
Table2 t2 INNER JOIN
Table1 t1 ON
CHARINDEX(t2.FirstName,t1.StudentName,0) > 0
AND
CHARINDEX(t2.LastName,t1.StudentName,0) > 0
AND
CHARINDEX(t2.StudentName,t1.StudentName,0) > 0
OR SELECT
*
FROM
Table2 t2 INNER JOIN
Table1 t1 ON
(t2.FirstName LIKE '%' + t1.StudentName + '%')
AND
(t2.MiddleName LIKE '%' + t1.StudentName + '%')
AND
(t2.LastName LIKE '%' + t1.StudentName + '%')
Hope it helps!
ASKER
That worked.
All this time and I never knew you could wrap a field with wildcards and use it in a LIKE! :-)
Thanks much.
All this time and I never knew you could wrap a field with wildcards and use it in a LIKE! :-)
Thanks much.
Glad to help..
Open in new window