FullTextSearch using a column against multiple columns.

Question

Hi All,

I am looking for fuzzy lookup / Fulltextsearch. Appreciate if someone could share some ideas on how to achieve this.

IF OBJECT_ID('tempdb..#Emp') IS NOT NULL  
  DROP TABLE #Emp;  
CREATE TABLE #Emp  
  (  
    ID           INT         NOT NULL  
   ,Department   VARCHAR(20) NOT NULL  
   ,EmployeeName VARCHAR(50) NOT NULL  
  );  
  
INSERT INTO #Emp  
  (  
    ID  
   ,Department  
   ,EmployeeName  
  )  
VALUES  
  (  
    1, 'Science', 'Andrew'  
  )  
 ,(  
    2, 'Maths', 'John'  
  )  
 ,(  
    3, 'Engineering', 'Matt'  
  );  
  
  
IF OBJECT_ID('tempdb..#lookup') IS NOT NULL  
  DROP TABLE #lookup;  
CREATE TABLE #lookup  
  (  
    ClassID       INT         NOT NULL  
   ,Department    VARCHAR(20) NOT NULL  
   ,EmployeeName1 VARCHAR(50) NULL  
   ,EmployeeName2 VARCHAR(50) NULL  
   ,EmployeeName3 VARCHAR(50) NULL  
   ,EmployeeName4 VARCHAR(50) NULL  
   ,EmployeeName5 VARCHAR(50) NULL  
   ,EmployeeName6 VARCHAR(50) NULL  
  );  
  
INSERT INTO [#lookup]  
  (  
    [ClassID]  
   ,[Department]  
   ,[EmployeeName1]  
   ,[EmployeeName2]  
   ,[EmployeeName3]  
   ,[EmployeeName4]  
   ,[EmployeeName5]  
   ,[EmployeeName6]  
  )  
VALUES  
  (  
    1             -- ID - int  
   ,'Science'     -- Department - varchar(20)  
   ,'Andrew Tate' -- EmployeeName1 - varchar(50)  
   ,'Christie P'  -- EmployeeName2 - varchar(50)  
   ,'William G'   -- EmployeeName3 - varchar(50)  
   ,'Frisco G'    -- EmployeeName4 - varchar(50)  
   ,'Austin H'    -- EmployeeName5 - varchar(50)  
   ,'Dallas P'    -- EmployeeName6 - varchar(50)  
  )  
 ,(  
    2         -- ID - int  
   ,'Maths'   -- Department - varchar(20)  
   ,'John K'  -- EmployeeName1 - varchar(50)  
   ,'Joe B'   -- EmployeeName2 - varchar(50)  
   ,'Aus B'   -- EmployeeName3 - varchar(50)  
   ,'Par U'   -- EmployeeName4 - varchar(50)  
   ,'Sap K'   -- EmployeeName5 - varchar(50)  
   ,'Ewr I'   -- EmployeeName6 - varchar(50)  
  )  
 ,(  
    3             -- ID - int  
   ,'Science'     -- Department - varchar(20)  
   ,NULL          -- EmployeeName1 - varchar(50)  
   ,NULL          -- EmployeeName2 - varchar(50)  
   ,'Andrew Tate' -- EmployeeName3 - varchar(50)  
   ,'Kelly Clark' -- EmployeeName4 - varchar(50)  
   ,'Frisco G'    -- EmployeeName5 - varchar(50)  
   ,'Jennifer R'  -- EmployeeName6 - varchar(50)  
  )  
 ,(  
    4             -- ID - int  
   ,'Engineering' -- Department - varchar(20)  
   ,'Matthew H'   -- EmployeeName1 - varchar(50)  
   ,'Smile K'     -- EmployeeName2 - varchar(50)  
   ,'Laugh G'     -- EmployeeName3 - varchar(50)  
   ,'Ram T'       -- EmployeeName4 - varchar(50)  
   ,'Jose P'      -- EmployeeName5 - varchar(50)  
   ,'Mike D'      -- EmployeeName6 - varchar(50)  
  )  
 ,(  
    5           -- ID - int  
   ,'Politics'  -- Department - varchar(20)  
   ,NULL        -- EmployeeName1 - varchar(50)  
   ,'Sush E'    -- EmployeeName2 - varchar(50)  
   ,'Adi H'     -- EmployeeName3 - varchar(50)  
   ,'Flow H'    -- EmployeeName4 - varchar(50)  
   ,'Air I'     -- EmployeeName5 - varchar(50)  
   ,'Water A'   -- EmployeeName6 - varchar(50)  
  )  
 ,(  
    6         -- ID - int  
   ,'Maths'   -- Department - varchar(20)  
   ,'Joe B'   -- EmployeeName1 - varchar(50)  
   ,'Par U'   -- EmployeeName2 - varchar(50)  
   ,'Sap K'   -- EmployeeName3 - varchar(50)  
   ,NULL      -- EmployeeName4 - varchar(50)  
   ,NULL      -- EmployeeName5 - varchar(50)  
   ,'John K'  -- EmployeeName6 - varchar(50)  
  );  
  
  
  

I want match #emp table against #lookup table on Department and get the full name of the employee. sample output shown below.

TIA!

Env - Azure SQL

Answer 1

Hi @PS
Try this query:

;WITH CTE AS  
(  
 SELECT Department,C.EmployeeName   
 FROM #lookup CROSS APPLY (VALUES(EmployeeName1),(EmployeeName2),(EmployeeName3),  
                                 (EmployeeName4),(EmployeeName5),(EmployeeName6))C(EmployeeName)   
)  
SELECT DISTINCT E.Department,C.EmployeeName  
FROM #Emp E JOIN CTE C ON E.Department=C.Department  
WHERE C.EmployeeName LIKE E.EmployeeName+'%'  

Best regards,
LiHong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.