SQL Server : run query on each row of a query to find a match in another table

Question

Using SQL Server, I am querying a list of people and photos taken from one system. There can be multiple photos of the same person. The attributes are the first name, last name, date of birth,SSN, etc...

SELECT * 
FROM MASTERPHOTOSDB

Output:

Master  | PhotoPath  | AQUIRE_TM | LastName | FirstName  | MiddleName | DOB      | DL
471578  | PHOTO72044 |1899-12-30 | PEREZ    | JOSE       | C          |4/15/2000 |TX12345678
468893  | PHOTO72049 |1899-12-30 | JOHNSON  | TRENT      |            |          |TX87654321
56553   | PHOTO72055 |1899-12-30 | SMITH    | JOHN       | D          |1/1/1990  |TX11223344
56553   | PHOTO72056 |1899-12-30 | SMITH    | JOHN       | D          |1/1/1990  |TX11223344

With that, I am trying to search another system for the same person based on certain criteria as a match:

last name, first name, middle name, DOB, DL (if not match go to 2)
last name, first name, DOB, DL (if no match go to 3)
Last name, first name, SSN, etc (if no match end search, go to next photo)

Below is a piece of the query that looks for matches with an IF statement(just a sample of the entire query):

 IF (SELECT TOP (1) COUNT(*)
     FROM [InformHelper].[dbo].[InformMasterNamesTST]
     WHERE [LastName] = 'SMITH' 
       AND [FirstName] = 'JOHN' 
       AND DOB = '01/01/1990' 
       AND DL = 'TX12345678') > 0    
    SELECT TOP (1) *
    FROM [InformHelper].[dbo].[InformMasterNamesTST]
    WHERE [LastName] = 'SMITH' 
      AND [FirstName] = 'JOHN' 
      AND DOB = '01/01/1990' 
      AND DL = 'TX12345678') > 0    
    ORDER BY [NamelastUpdated] DESC, [NameEnterDate] DESC
ELSE IF (SELECT TOP (1) COUNT(*)
         FROM [InformHelper].[dbo].[InformMasterNamesTST]
     WHERE [LastName] = 'SMITH' 
       AND [FirstName] = 'JOHN' 
       AND DOB = '01/01/1990' 
       AND SSN = '12345678901') > 0      
    SELECT TOP (1) *
    FROM [InformHelper].[dbo].[InformMasterNamesTST]
    WHERE [LastName] = 'SMITH' 
      AND [FirstName] = 'JOHN' 
      AND DOB = '01/01/1990' 
      AND SSN = '12345678901') > 0      
    ORDER BY [NamelastUpdated] DESC, [NameEnterDate] DESC
ELSE 
    SELECT 0

Which would return the below from my second table:

Name     | LastName | FirstName | MiddleName | SEX | RACE | DOB         | DL | SSN
10173538  | SMITH    | JOHN      | SHAWN      | M   | W    | 01/01/1990 |     | 1234567890

Basically I would like it to do a "for each" for each name in the first table to search the IF statement I created. I'm getting confused on how to query each name in the IF statement. Since some names do not have a DL, SSN, Middle name, DOB, etc if have to make sure it checks for possible matches.

If it finds a match I want it to "do something". Which I want it to be able to reference the photo path column from the first table. So I somehow need to pass that to the "do something" part.

What you want to do is a JOIN. Read up on that and try to modify your query. — user1443098, Sep 12 '18 at 14:33
SQL isn't necessarily your best bet for doing fuzzy matching. For instance, see this answer to a similar question. https://stackoverflow.com/a/9731894/5790584 — Eric Brandt, Sep 12 '18 at 14:55
so it appears that you used my approach and found it useful? — Vladislav, Oct 16 '18 at 21:04

score 0 · Answer 1 · answered Sep 12 '18 at 15:10

I think you can do what you want with COALESCE and the three joins COALESCE will basically look for the first non NULL item, so if the join fails it will go to the next one

The SQL would look something like

  SELECT
     Name           = COALESCE(S1.Name, S2.Name, S3.Name)
     LastName       = COALESCE(S1.LastName, S2.LastName, S3.LastName)
     FirstName      = COALESCE(S1.FirstName, S2.FirstName, S3.FirstName)
     MiddleName     = COALESCE(S1.MiddleName, S2.MiddleName, S3.MiddleName)
     SEX            = COALESCE(S1.SEX, S2.SEX, S3.SEX)
     RACE           = COALESCE(S1.RACE, S2.RACE, S3.RACE)
     DOB            = COALESCE(S1.DOB, S2.DOB, S3.DOB)
     DL             = COALESCE(S1.DL, S2.DL, S3.DL)
     SSN            = COALESCE(S1.SSN, S2.SSN, S3.SSN)
  FROM MASTERPHOTODB MPDB

  LEFT JOIN SECONDSYSTEM S1
    ON  S1.LastName     = MPDB.LastName
    AND S1.FirstName    = MPDB.FirstName
    AND S1.MiddleName   = MPDB.MiddleName
    AND S1.DOV          = MPDB.DOB
    AND S1.DL           = MPDB.DL


  LEFT JOIN SECONDSYSTEM S2
    ON  S2.LastName     = MPDB.LastName
    AND S2.FirstName    = MPDB.FirstName
    AND S2.DOB          = MPDB.DOB
    AND S2.DL           = MPDB.DL

  LEFT JOIN SECONDSYSTEM S3
    ON  S3.LastName     = MPDB.LastName
    AND S3.FirstName    = MPDB.FirstName
    AND S3.SSN          = MPDB.SSN

  WHERE 1=1
    AND MPDB.LastName = 'SMITH'
    AND MPDB.FirstName = 'JOHN'
    AND MPDB.DOB = '01/01/1990' 
    AND MPDB.DL = 'TX12345678'

that should give you the data set, you'd then need to cursor through the elements returned

score 0 · Answer 2 · answered Sep 12 '18 at 15:17

I would suggest something like that, but not sure if I have understood your idea completely. Please add more input and if necessary I will update my answer. In general I would like to uniquely identify all people by various search criteria and then "do something" with them:

DECLARE @userUniqueIDs TABLE (UniqueId NVARCHAR(100)); --could be multiple values of any data type
INSERT @userUniqueIDs
SELECT UniqueId
FROM MASTERPHOTOSDB MPDB
JOIN [InformHelper].[dbo].[InformMasterNamesTST] IMNTST 
ON <SearchCondition_1>;

INSERT @userUniqueIDs
SELECT UniqueId
FROM MASTERPHOTOSDB MPDB
JOIN [InformHelper].[dbo].[InformMasterNamesTST] IMNTST 
ON <SearchCondition_2>
WHERE UniqueId NOT IN (SELECT UniqueId FROM @userUniqueIDs);

INSERT @userUniqueIDs
SELECT UniqueId
FROM MASTERPHOTOSDB MPDB
JOIN [InformHelper].[dbo].[InformMasterNamesTST] IMNTST 
ON <SearchCondition_3>
WHERE UniqueId NOT IN (SELECT UniqueId FROM @userUniqueIDs);

<do_something with identified people>

No matter what is in the "do something" you should in all cases try to do it in a bulk approach - skip any kinds of loops e.g. WHILE or CURSOR.

Hope this helps.

So I understand. the @userUniqueIDs are supposed to be the attributes being compared on the first table? — user1846586, Sep 12 '18 at 15:59
`UniqueId` should be a single or multiple fields that uniquely identifies your people — Vladislav, Sep 13 '18 at 07:01

score 0 · Answer 3 · answered Sep 12 '18 at 16:17

I'd likely use a CASE statement in your output to determine who matches what conditions in the other system.

Below is an mocked up example based upon Oracle, but just remove the FROM DUAL type statements and you'll get an idea of how it works in SQL Server too.

;WITH Data AS (
SELECT 471578 AS Master,  'PHOTO72044' AS PhotoPath,  '1899-12-30' AS AQUIRE_TM,  'PEREZ' AS LastName, 'JOSE' AS FirstName,  'C' AS MiddleName, '4/15/2000' AS DOB, 'TX12345678' AS DL FROM DUAL UNION ALL
SELECT 468893, 'PHOTO72049', '1899-12-30', 'JOHNSON', 'TRENT', '', '', 'TX87654321' FROM DUAL UNION ALL
SELECT 56553, 'PHOTO72055', '1899-12-30', 'SMITH', 'JOHN', 'D', '1/1/1990', 'TX11223344' FROM DUAL UNION ALL
SELECT 56553, 'PHOTO72056', '1899-12-30', 'SMITH', 'JOHN', 'D', '1/1/1990', 'TX11223344' FROM DUAL  
), MockOtherDataSource AS (
SELECT 471578 AS Master,  'PHOTO72044' AS PhotoPath,  '1899-12-30' AS AQUIRE_TM,  'PEREZ' AS LastName, 'JOSE' AS FirstName,  'C' AS MiddleName, '4/15/2000' AS DOB, 'TX12345678' AS DL FROM DUAL UNION ALL
SELECT 468893, 'PHOTO72049', '1899-12-30', 'JOHNSON', 'TRENT', '', '', 'TX87654321' FROM DUAL UNION ALL
SELECT 56553, 'PHOTO72055', '1899-12-30', 'SMITHY', 'JOHN', 'D', '1/1/1990', 'TX11223344' FROM DUAL UNION ALL
SELECT 56553, 'PHOTO72056', '1899-12-30', 'SMITH', 'JOHN', 'D', '1/1/1990', 'TX11223344' FROM DUAL UNION ALL
SELECT 56553, 'PHOTO72056', '1899-12-30', 'SMITH', 'JOHN', 'B', '1/1/1990', 'TX11223344' FROM DUAL  
), Combined AS (
SELECT 
    Data.Master,
    Data.PhotoPath,
    Data.FirstName,
    Data.MiddleName,
    Data.LastName, 
    Data.DL,
    CASE 
        -- Here is where you add your conditions to determine the "Match Type" 
        WHEN Data.FirstName = MockOtherDataSource.FirstName AND
             Data.LastName = MockOtherDataSource.LastName AND
             Data.MiddleName = MockOtherDataSource.MiddleName AND
             Data.DOB = MockOtherDataSource.DOB AND
             Data.DL = MockOtherDataSource.DL THEN 1
        WHEN Data.FirstName = MockOtherDataSource.FirstName AND
             Data.LastName = MockOtherDataSource.LastName AND
             Data.DOB = MockOtherDataSource.DOB AND
             Data.DL = MockOtherDataSource.DL AND 
             (Data.MiddleName <> MockOtherDataSource.MiddleName 
              OR (Data.MiddleName IS NULL AND MockOtherDataSource.MiddleName IS NOT NULL) 
              OR (Data.MiddleName IS NOT NULL AND MockOtherDataSource.MiddleName IS NULL)) THEN 2
        -- Similar match conditions here for other join conditions you're interested in
    END AS MatchType,
    MockOtherDataSource.Master AS Master1,
    MockOtherDataSource.PhotoPath AS PhotoPath1,
    MockOtherDataSource.FirstName AS FirstName1,
    MockOtherDataSource.MiddleName AS MiddleName1,
    MockOtherDataSource.LastName AS LastName1, 
    MockOtherDataSource.DL AS DL1
FROM 
    Data,
    MockOtherDataSource
) SELECT * FROM Combined WHERE MatchType IS NOT NULL
;

score 0 · Accepted Answer · answered Sep 12 '18 at 19:03

Going from what was suggested:

DECLARE @userUniqueIDs TABLE (Inform_MasterName NVARCHAR(100), PhotoPath NVARCHAR(100)); --could be multiple values of any data type
INSERT @userUniqueIDs
SELECT IMNTST.Inform_MasterName, MPDB.PhotoPath
FROM [InformHelper].[dbo].[LTMasterNamesAndPhotosYesterday] MPDB
JOIN [InformHelper].[dbo].[InformMasterNamesTST] IMNTST 
ON  IMNTST.LastName     = MPDB.LastName collate SQL_Latin1_General_CP1_CI_AS 
    AND IMNTST.MiddleName    = MPDB.MiddleName collate SQL_Latin1_General_CP1_CI_AS 
    AND IMNTST.FirstName    = MPDB.FirstName collate SQL_Latin1_General_CP1_CI_AS 
    AND IMNTST.SEX          = MPDB.SEX collate SQL_Latin1_General_CP1_CI_AS 
    AND IMNTST.RACE           = MPDB.RACE collate SQL_Latin1_General_CP1_CI_AS 
    AND IMNTST.DOB          = MPDB.DOB collate SQL_Latin1_General_CP1_CI_AS 


INSERT @userUniqueIDs
SELECT Inform_MasterName, MPDB.PhotoPath
FROM [InformHelper].[dbo].[LTMasterNamesAndPhotosYesterday] MPDB
JOIN [InformHelper].[dbo].[InformMasterNamesTST] IMNTST 
ON  IMNTST.LastName     = MPDB.LastName collate SQL_Latin1_General_CP1_CI_AS 
    AND IMNTST.FirstName    = MPDB.FirstName collate SQL_Latin1_General_CP1_CI_AS 
    AND IMNTST.SEX          = MPDB.SEX collate SQL_Latin1_General_CP1_CI_AS 
    AND IMNTST.RACE           = MPDB.RACE collate SQL_Latin1_General_CP1_CI_AS 
    AND IMNTST.DL           = MPDB.DL collate SQL_Latin1_General_CP1_CI_AS 
WHERE Inform_MasterName NOT IN (SELECT Inform_MasterName FROM @userUniqueIDs);

INSERT @userUniqueIDs
SELECT Inform_MasterName, MPDB.PhotoPath
FROM [InformHelper].[dbo].[LTMasterNamesAndPhotosYesterday] MPDB
JOIN [InformHelper].[dbo].[InformMasterNamesTST] IMNTST 
ON  IMNTST.LastName     = MPDB.LastName collate SQL_Latin1_General_CP1_CI_AS 
    AND IMNTST.FirstName    = MPDB.FirstName collate SQL_Latin1_General_CP1_CI_AS
    AND IMNTST.DL           = MPDB.DL collate SQL_Latin1_General_CP1_CI_AS
    AND IMNTST.DOB          = MPDB.DOB collate SQL_Latin1_General_CP1_CI_AS 
WHERE Inform_MasterName NOT IN (SELECT Inform_MasterName FROM @userUniqueIDs);

INSERT @userUniqueIDs
SELECT Inform_MasterName, MPDB.PhotoPath
FROM [InformHelper].[dbo].[LTMasterNamesAndPhotosYesterday] MPDB
JOIN [InformHelper].[dbo].[InformMasterNamesTST] IMNTST 
ON  IMNTST.LastName     = MPDB.LastName collate SQL_Latin1_General_CP1_CI_AS 
    AND IMNTST.SSN           = MPDB.SSN collate SQL_Latin1_General_CP1_CI_AS
    AND IMNTST.DOB          = MPDB.DOB collate SQL_Latin1_General_CP1_CI_AS 
WHERE Inform_MasterName NOT IN (SELECT Inform_MasterName FROM @userUniqueIDs);

I did have to find unique values from the PhotoPath column because in some instances there were duplicates in the PhotoPath. I used the below:

select Inform_MasterName,
       PhotoPath
from (
SELECT Inform_MasterName,
       PhotoPath,
       ROW_NUMBER()OVER(PARTITION BY PhotoPath ORDER BY Inform_MasterName DESC) rn
       from @userUniqueIDs
       ) a 
       where rn = 1

OUTPUT:

**Inform_MasterName | PhotoPath**
21911               | L:\Images\57114.JPG                               
210392              | L:\Images\57115.JPG                               
79489               | L:\Images\PHOTO72431.jpg                          
19975               | L:\Images\PHOTO72499.jpg                          
111132              | L:\Images\PHOTO72509.jpg                          
105377              | L:\Images\PHOTO72510.jpg                          
10230182            | L:\Images\PHOTO72529.jpg                          
37933               | L:\Images\PHOTO72533.jpg                          
36966               | L:\Images\PHOTO72540.jpg                          
10223896            | L:\Images\PHOTO72550.jpg                          
10223896            | L:\Images\PHOTO72551.jpg

SQL Server : run query on each row of a query to find a match in another table

4 Answers4