TSQL and joining 2 views to create rows where no relationship

SimonCHere · 23 Jan 2020 at 11:34

I mean it's not a nice bit of code but...

Code:

CREATE TABLE #v1
(
    KBID VARCHAR(50),
);

INSERT INTO #v1
 VALUES ('KB1'),
('KB2'),
('KB3'),
('KB4'),
('KB5');

CREATE TABLE #v2
(
    servername VARCHAR(50),
    servergroup VARCHAR(50),
    kbid VARCHAR(50),
    installDate varchar(10)
);

insert into #v2
values ('A','A','KB1','1/1/2020'),
('B','B','KB2','1/1/2020'),
('B','B','KB3','1/1/2020'),
('C','C','KB5','1/1/2020');


with t (kbid, gap) as (select distinct kbid, 1 from #v1)
, t1 (sn,sg, gap) as (select distinct servername,servergroup, 1 from #v2)
select t.kbid, t1.sn, t1.sg, isnull(b.installDate,'NA')
from t
full outer join t1 on t.gap = t1.gap
left join #v2 b on b.kbid = t.kbid and b.servername = t1.sn
order by t.kbid, t1.sn;

drop table #v1;
drop table #v2;

Dj_Jestar · 23 Jan 2020 at 11:58

just filter the left join query you already have, surely?

Code:

WHERE View2.InstallDate IS NULL

SimonCHere · 23 Jan 2020 at 12:09

Dj_Jestar said:
just filter the left join query you already have, surely?

Code:

WHERE View2.InstallDate IS NULL

The problems is you need to force a cartesian join on distinct rows. You can use MSSQL "CROSS JOIN" to do this as well.

Code:

select distinct t.kbid, t1.servername, t1.servergroup , isnull(b.installDate,'NA')
from #v1 t
cross join #v2 t1
left join #v2 b on b.kbid = t.kbid and b.servername = t1.servername
order by t.KBID, t1.servername

darkgen · 23 Jan 2020 at 16:42

SimonCHere said:

I mean it's not a nice bit of code but...

Code:

CREATE TABLE #v1
(
    KBID VARCHAR(50),
);

INSERT INTO #v1
 VALUES ('KB1'),
('KB2'),
('KB3'),
('KB4'),
('KB5');

CREATE TABLE #v2
(
    servername VARCHAR(50),
    servergroup VARCHAR(50),
    kbid VARCHAR(50),
    installDate varchar(10)
);

insert into #v2
values ('A','A','KB1','1/1/2020'),
('B','B','KB2','1/1/2020'),
('B','B','KB3','1/1/2020'),
('C','C','KB5','1/1/2020');


with t (kbid, gap) as (select distinct kbid, 1 from #v1)
, t1 (sn,sg, gap) as (select distinct servername,servergroup, 1 from #v2)
select t.kbid, t1.sn, t1.sg, isnull(b.installDate,'NA')
from t
full outer join t1 on t.gap = t1.gap
left join #v2 b on b.kbid = t.kbid and b.servername = t1.sn
order by t.kbid, t1.sn;

drop table #v1;
drop table #v2;

Ooooh; perfecto. Let me have a play with the actual views and see if I can plug it all in. Many thanks!

SimonCHere · 23 Jan 2020 at 17:11

darkgen said:
Ooooh; perfecto. Let me have a play with the actual views and see if I can plug it all in. Many thanks!

Use the CROSS_JOIN one I posted later. It looks better!

darkgen · 23 Jan 2020 at 17:57

SimonCHere said:
Use the CROSS_JOIN one I posted later. It looks better!

Ooh ok; I managed to plug it in with the previous query but I do get some anomalies. I'll have a play tomorrow now as I've already got a large goblet of Baileys on the go!

Many thanks for assistance!

E: The crossjoin removes the anomalies! thank you; I'll keep this method down for future like requests!