Help with the query

  • 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

  • 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

  • Thanks Erirkur for the help.

    But i had miss quoted my question in previous screnario.

  • 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?

  • 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

  • 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.

  • 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

  • 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.

  • 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