Post reply

Tricky sql join

  • October 30, 2015 at 3:32 pm

    #322220

    I have two tables #main and #other . i want them to join on startdate ,kpi and tables columns and i want to get all the rows whether they are matching or not. I want null if they are not matching.

    create table #main(Startdate date , [tables] varchar(10),node varchar(10),kpi_main varchar(10), kpi_value_main varchar(10))

    insert into #main values ('2015-10-22','all','NTT',1,'robert'),('2015-10-22','all','NTT',2,'Sam'),('2015-10-22','all','NTT',3,'mike')

    ,('2015-10-22','all','SO1',1,'robert'),('2015-10-22','all','SO1',3,'mike'),('2015-10-22','all','SO1',5,'james')

    create table #other (Startdate date , [tables] varchar(10),node varchar(10),kpi_other varchar(10), kpi_value_other varchar(10))

    insert into #other values ('2015-10-22','all','KLM',1,'robert'),('2015-10-22','all','KLM',3,'mike'),('2015-10-22','all','KLM',5,'james')

    select * from #main

    select * from #other

    The result should be like below. please paste in excel to preview

    Startdate tables node kpi_main kpi_value_main Startdate tables node kpi_other kpi_value_other

    10/22/2015 all NTT 1 robert 10/22/2015 all KLM 1 robert

    10/22/2015 all NTT 2 Sam null null null null null

    10/22/2015 all NTT 3 mike 10/22/2015 all KLM 3 mike

    null null null null 10/23/2015 all KLM 5 james

    10/22/2015 all SO1 1 robert 10/22/2015 all KLM 1 robert

    10/22/2015 all SO1 3 mike 10/22/2015 all KLM 3 mike

    10/22/2015 all SO1 5 james 10/22/2015 all KLM 5 james

  • October 30, 2015 at 5:40 pm

    #1836847

    Your result doesn't appear to match your data but a FULL JOIN (a type of OUTER join) is what you want. Like this...

    SELECT m.*, o.*

    FROM #Main m

    FULL JOIN #Other o

    ON m.Startdate = o.Startdate

    AND m.kpi_main = o.kpi_other

    AND m.[tables] = o.[tables]

    ;

    Result:

    Startdate tables node kpi_main kpi_value_main Startdate tables node kpi_other kpi_value_other

    ---------- ---------- ---------- ---------- -------------- ---------- ---------- ---------- ---------- ---------------

    2015-10-22 all NTT 1 robert 2015-10-22 all KLM 1 robert

    2015-10-22 all NTT 2 Sam NULL NULL NULL NULL NULL

    2015-10-22 all NTT 3 mike 2015-10-22 all KLM 3 mike

    2015-10-22 all SO1 1 robert 2015-10-22 all KLM 1 robert

    2015-10-22 all SO1 3 mike 2015-10-22 all KLM 3 mike

    2015-10-22 all SO1 5 james 2015-10-22 all KLM 5 james

    (6 row(s) affected)

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • November 2, 2015 at 2:29 pm

    #1837218

    Thanks for replying .

    I am able to get the result using Select *

    From

    (

    Select *

    ,DENSE_RANK() over(Order by #main.node) as NR_main

    From #main

    ) as mrank

    full outer join

    (

    Select #Other.*

    ,DENSE_RANK() over(order by nm.node) as NR_other

    From

    (

    Select Distinct node

    From #main

    ) nm

    cross join #other

    ) cj

    on mrank.Startdate = cj.Startdate

    and mrank.tables = cj.tables

    and mrank.kpi_main = cj.kpi_other

    and mrank.NR_main = cj.NR_other

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply