September 26, 2016 at 10:01 pm
I have the below tables and want the result mention in the result set
DROP TABLE #SourceData
DROP TABLE #ReferenceTable1
DROP TABLE #ReferenceTable2
DROP TABLE #ResultThatIAmLooking
CREATE TABLE #SourceData(
Name VARCHAR(50),
City VARCHAR(50),
State VARCHAR(50)
)
CREATE TABLE #ReferenceTable1(
ReferenceName1 VARCHAR(50),
Address1 VARCHAR(100),
Address2 VARCHAR(100)
)
CREATE TABLE #ReferenceTable2(
ReferenceName2 VARCHAR(50),
City VARCHAR(100),
State VARCHAR(100)
)
CREATE TABLE #ResultThatIAmLooking(
Name VARCHAR(50),
City VARCHAR(50),
State VARCHAR(50),
RD1_ReferenceName1 VARCHAR(50),
RD1_Address1 VARCHAR(100),
RD1_Address2 VARCHAR(100),
RD2_ReferenceName2 VARCHAR(50),
RD2_City VARCHAR(100),
RD2_State VARCHAR(100)
)
--SELECT * FROM #SourceData
--SELECT * FROM #ReferenceTable1
--SELECT * FROM #ReferenceTable2
--SELECT
--DISTINCT s.Name
--,s.City
--,s.State
--,r1.ReferenceName1
--,r1.Address1
--,r1.Address2
--,r2.ReferenceName2
--,r2.City AS R2_City
--,r2.State AS R2_State
--FROM #SourceData s
--LEFT JOIN #ReferenceTable1 r1 ON LEFT(s.Name,CHARINDEX(' ',s.Name)) = LEFT(r1.ReferenceName1,CHARINDEX(' ',r1.ReferenceName1))
--LEFT JOIN #ReferenceTable2 r2 ON LEFT(s.Name,CHARINDEX(' ',s.Name)) = LEFT(r2.ReferenceName2,CHARINDEX(' ',r2.ReferenceName2))
INSERT INTO #SourceData (Name,City,State)
VALUES('Pat Services','Pune','MH')
INSERT INTO #SourceData (Name,City,State)
VALUES('American Cloths','Pune','MH')
INSERT INTO #ReferenceTable1 (ReferenceName1 ,Address1 ,Address2 )
VALUES ('Pat Serives Private Limited','ZZZ','QQQ')
INSERT INTO #ReferenceTable1 (ReferenceName1 ,Address1 ,Address2 )
VALUES ('Pat Serives Limited','ZZ','Q')
INSERT INTO #ReferenceTable1 (ReferenceName1 ,Address1 ,Address2 )
VALUES ('American Cloths','ZZ','Q')
INSERT INTO #ReferenceTable1 (ReferenceName1 ,Address1 ,Address2 )
VALUES ('American Expree','ZZ','Q')
INSERT INTO #ReferenceTable2 (ReferenceName2 ,City ,State )
VALUES('American Pesticide Service','Pune','MH')
INSERT INTO #ReferenceTable2 (ReferenceName2 ,City ,State )
VALUES('American Express','Mum','JK')
INSERT INTO #ResultThatIAmLooking (Name ,City ,State ,RD1_ReferenceName1 ,RD1_Address1 ,RD1_Address2 ,RD2_ReferenceName2 ,RD2_City ,RD2_State )
VALUES('Pat Services','Pune','MH','Pat Serives Private Limited','ZZZ','QQQ',NULL,NULL,NULL)
INSERT INTO #ResultThatIAmLooking (Name ,City ,State ,RD1_ReferenceName1 ,RD1_Address1 ,RD1_Address2 ,RD2_ReferenceName2 ,RD2_City ,RD2_State )
VALUES('Pat Services','Pune','MH','Pat Serives Limited','ZZ','Q',NULL,NULL,NULL)
INSERT INTO #ResultThatIAmLooking (Name ,City ,State ,RD1_ReferenceName1 ,RD1_Address1 ,RD1_Address2 ,RD2_ReferenceName2 ,RD2_City ,RD2_State )
VALUES('American Cloths','Pune','MH','American Cloths','ZZ','Q','American Pesticide Service','Pune','MH')
INSERT INTO #ResultThatIAmLooking (Name ,City ,State ,RD1_ReferenceName1 ,RD1_Address1 ,RD1_Address2 ,RD2_ReferenceName2 ,RD2_City ,RD2_State )
VALUES('American Cloths','Pune','MH','American Cloths','ZZ','Q','American Express','Pune','MH')
Result such that :
1. we would consider only first word before space in SourceData table and search into ReferenceTable1 and Referencetable2.
If found the record then we would mention it.
Edited : In the feet of sleep maybe i quoted the wrong question. Have updated. any help on this would be appreciated. Thanks
September 26, 2016 at 11:27 pm
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
September 27, 2016 at 3:40 am
Thanks Erirkur for the help.
But i had miss quoted my question in previous screnario.
September 27, 2016 at 4:09 am
JackTimber (9/27/2016)
Thanks Erirkur for the help.But i had miss quoted my question in previous screnario.
I thought there was something odd with desired results 😉
😎
Here is a solution to your problem which uses the same JOIN_WORD method as the previous code
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
)
SELECT
SD.Name
,SD.City
,SD.State
,FR.ReferenceName1
,FR.Address1
,FR.Address2
,SR.ReferenceName2
,SR.City
,SR.State
FROM SAMPLE_DATA SD
LEFT OUTER JOIN FIRST_REFERENCE FR
ON SD.JOIN_WORD = FR.JOIN_WORD
LEFT OUTER JOIN SECOND_REFERENCE SR
ON SD.JOIN_WORD = SR.JOIN_WORD;
Output using 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 American Pesticide Service Pune MH
Question, do you understand how this method works as if you implement it then you will have to maintain it?
September 27, 2016 at 4:17 am
SELECT
s.Name
,s.City
,s.State
,r1.ReferenceName1
,r1.Address1
,r1.Address2
,r2.ReferenceName2
,r2.City AS R2_City
,r2.State AS R2_State
FROM #SourceData s
LEFT JOIN #ReferenceTable1 r1 ON LEFT(s.Name,CHARINDEX(' ',s.Name)) = LEFT(r1.ReferenceName1,CHARINDEX(' ',r1.ReferenceName1))
LEFT JOIN #ReferenceTable2 r2 ON LEFT(s.Name,CHARINDEX(' ',s.Name)) = LEFT(r2.ReferenceName2,CHARINDEX(' ',r2.ReferenceName2))
John
September 27, 2016 at 6:36 am
Hey all,
Thanks for the response. But Left Join would not work in my case. I have updated the test data to represent my scenario very closely.
Any pointer on that would be helpfull. Thanks.
September 27, 2016 at 7:08 am
Gosh, the goalposts have moved for a second time. It's not the LEFT JOIN - just put a DISTINCT after the SELECT. That will give the results you specified.
John
September 27, 2016 at 7:26 am
Hi John,
Goalpost moved again 🙂
Distinct would work fine for the small data set i have provided, but i am dealing with data with millions of rows and 50 column. Distinct would not work there.
Just to give you a feel i have change the City and Street data columns. I want to hear from you guys what how do you deal when there is no uniquesnessa and you have to deal with "Word" to reflect the data.
September 27, 2016 at 7:58 am
Your business rules don't make any sense to me. Why, when you have different values in ReferenceTable2 for City and for State, do the two rows in your expected results have the same values for RD2_City and for RD2_State?
John
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply