TSQL and joining 2 views to create rows where no relationship

[SimonCHere]

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

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]

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

WHERE View2.InstallDate IS NULL

 

[SimonCHere]

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

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.

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]

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

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]

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]

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!