Tricky sql join

Question

Post reply

Tricky sql join

sqlserver38

SSC Veteran

Points: 276
More actions
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
Startdatetablesnodekpi_mainkpi_value_mainStartdatetablesnodekpi_otherkpi_value_other
10/22/2015allNTT1robert10/22/2015allKLM1robert
10/22/2015allNTT2Samnullnullnullnullnull
10/22/2015allNTT3mike10/22/2015allKLM3mike
nullnullnullnull10/23/2015allKLM5james
10/22/2015allSO11robert10/22/2015allKLM1robert
10/22/2015allSO13mike10/22/2015allKLM3mike
10/22/2015allSO15james10/22/2015allKLM5james

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

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

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 1

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)

sqlserver38 SSC Veteran Points: 276 More actions · Answer 2

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