The design and the desired results are somewhat odd as it requires a union of the reference data.
😎
USE TEEST;
GO
SET NOCOUNT ON;
--
IF OBJECT_ID(N'tempdb..#SourceData') IS NOT NULL DROP TABLE #SourceData;
CREATE TABLE #SourceData(
Name VARCHAR(50),
City VARCHAR(50),
State VARCHAR(50)
);
IF OBJECT_ID(N'tempdb..#ReferenceTable1') IS NOT NULL DROP TABLE #ReferenceTable1;
CREATE TABLE #ReferenceTable1(
ReferenceName1 VARCHAR(50),
Address1 VARCHAR(100),
Address2 VARCHAR(100)
);
IF OBJECT_ID(N'tempdb..#ReferenceTable2') IS NOT NULL DROP TABLE #ReferenceTable2;
CREATE TABLE #ReferenceTable2(
ReferenceName2 VARCHAR(50),
City VARCHAR(100),
State VARCHAR(100)
);
INSERT INTO #SourceData (Name,City,State)
VALUES ('Pat Services','Pune','MH')
,('American Cloths','Pune','MH');
INSERT INTO #ReferenceTable1 (ReferenceName1 ,Address1 ,Address2 )
VALUES ('Pat Serives Private Limited','ZZZ','QQQ')
,('Pat Serives Limited','ZZ','Q')
,('American Cloths','ZZ','Q');
INSERT INTO #ReferenceTable2 (ReferenceName2 ,City ,State )
VALUES('American Pesticide Service','Pune','MH');
;WITH SAMPLE_DATA AS
(
SELECT
SD.Name
,LEFT(SD.Name,CHARINDEX(CHAR(32),SD.Name,1)) AS JOIN_WORD
,SD.City
,SD.State
FROM #SourceData SD
)
,FIRST_REFERENCE AS
(
SELECT
RT1.ReferenceName1
,LEFT(RT1.ReferenceName1,CHARINDEX(CHAR(32),RT1.ReferenceName1,1)) AS JOIN_WORD
,RT1.Address1
,RT1.Address2
FROM #ReferenceTable1 RT1
)
,SECOND_REFERENCE AS
(
SELECT
RT2.ReferenceName2
,LEFT(RT2.ReferenceName2,CHARINDEX(CHAR(32),RT2.ReferenceName2,1)) AS JOIN_WORD
,RT2.City
,RT2.State
FROM #ReferenceTable2 RT2
)
,JOINED_REFERENCES AS
(
SELECT
FREF.JOIN_WORD
,FREF.ReferenceName1
,FREF.Address1
,FREF.Address2
,NULL AS ReferenceName2
,NULL AS City
,NULL AS State
FROM FIRST_REFERENCE FREF
UNION ALL
SELECT
SREF.JOIN_WORD
,NULL
,NULL
,NULL
,SREF.ReferenceName2
,SREF.City
,SREF.State
FROM SECOND_REFERENCE SREF
)
SELECT
SD.Name
,SD.City
,SD.State
,JR.ReferenceName1
,JR.Address1
,JR.Address2
,JR.ReferenceName2
,JR.City
,JR.State
FROM SAMPLE_DATA SD
LEFT OUTER JOIN JOINED_REFERENCES JR
ON SD.JOIN_WORD = JR.JOIN_WORD;
Output from the sample data
Name City State ReferenceName1 Address1 Address2 ReferenceName2 City State
----------------- ----- ------ ---------------------------- --------- --------- --------------------------- ----- ------
Pat Services Pune MH Pat Serives Private Limited ZZZ QQQ NULL NULL NULL
Pat Services Pune MH Pat Serives Limited ZZ Q NULL NULL NULL
American Cloths Pune MH American Cloths ZZ Q NULL NULL NULL
American Cloths Pune MH NULL NULL NULL American Pesticide Service Pune MH