TSQL and joining 2 views to create rows where no relationship

[darkgen]

Title is awful but in a nutshell. 1 view contains KBIDs, 2nd contains list of servers, server groups and KBIDs and when installed.

I'm trying to create view(3) which will return every KBID and server, showing the install date column where a row exists in the 2nd view and "NA" or somesuch where a row does not. Probably as clear as MUD

View1

KBID
KB1
KB2
KB3
KB4
KB5

View2

Server    ServerGroup    KBID    InstallDate
A    A    KB1    1/1/2010
B    B    KB2    1/1/2010
B    B    KB3    1/1/2020
C    C    KB5    1/1/2020

View3

KBID    Server    ServerGroup    InstallDate
KB1    A    A    1/1/2020
KB1    B    B    NA
KB1    C    C    NA
KB2    A    A    NA
KB2    B    B    1/1/2020
KB2    C    C    NA
KB3    A    A    NA
KB3    B    B    1/1/2020
KB3    C    C    NA
KB4    A    A    NA
KB4    B    B    NA
KB4    C    C    NA
KB5    A    A    NA
KB5    B    B    NA
KB5    C    C    1/1/2020

Looks slightly mental when I view it like that!

TIA.

 

[chroniclard]

Whats the issue isnt it just left outer joins?

 

[glenimp617]

Cant you just do this in SCCM?

 

[darkgen]

No, that just creates additional KBID rows with NULLS.

SELECT        dbo.vw2.kbid, vw2.Server, dbo.vw1.ServerGroup
FROM            dbo.vw_v1 LEFT OUTER JOIN
                         dbo.vw1 ON dbo.vw2.kbid = dbo.vw1.kbid

Outputs:

8   NULL    NULL
9   NULL    NULL
10    NULL    NULL
11    NULL    NULL
12    NULL    NULL
13    Server    ServerGroup

 

[darkgen]

Cant you just do this in SCCM?

You mean SSMS (SQL Server Management Studio)? That's what I'm using

It's needs a subquery but I struggle with those :/

 

[glenimp617]

You mean SSMS (SQL Server Management Studio)? That's what I'm using

It's needs a subquery but I struggle with those :/

No, Microsoft system center config mgr

It might achieve the same end result if you need to know what's installed, where and in groups

 

[chroniclard]

The nulls = your N/A

 

[darkgen]

Oh you did mean SCCM! So, the irony here is that this exercise is actually trying to verify the data we get from Altiris which is often hit and miss. Although we will he moving to SCCM, I do not have any immediate access to the any of the Release management tools.

 

[darkgen]

The nulls = your N/A

No; it shows NULLS where the KBID is not installed on any server.

A return will only show where the row in view2, contains server with kbid installed. I want it to return an additional columns where a row does not exist in view2 that contains a server row for that kbid. Probably as clear as mud

 

[snips86x]

Mocked up your dataset and you can do it like this:

select * From Servers S
FULL outer JOIN Code C on s.KBID = C.KBID

Those which are not in the servers table with upgrade dates will appear as NULL for the columns where there is no matching data, but the KBID will still be visible in the result set

 

[chroniclard]

^^ Same result as left outer join

 

[darkgen]

Mocked up your dataset and you can do it like this:

Those which are not in the servers table with upgrade dates will appear as NULL for the columns where there is no matching data, but the KBID will still be visible in the result set

So, to be clear. The result will include Server, ServerGroup, KBID and Install = NULL / NA?

If you look at the OP View3; that's what I'm after.

 

[snips86x]

So, to be clear. The result will include Server, ServerGroup, KBID and Install = NULL / NA?

Yes but only NULL where there aren't records to match.I would send a screenshot but the hosting sites are blocked at work.

You could CASE NULL to be N/A if you prefer.

 

[chroniclard]

View3 has data not in View1 and 2.......or is there more?

 

[snips86x]

View3 has data not in View1 and 2.......or is there more?

Might be the join he's done or mock up

 

[darkgen]

So, View3 is where I went to get to from the data contained in View1 and View2.

 

[chroniclard]

Looks like you are making up data that isnt there to me.....

 

[darkgen]

Looks like you are making up data that isnt there to me.....

Heh, you may be right! Rechecking the OP, it looks er OK?! I must be going blind (which I am).
So View3 is what I'm after from the data available in V1 and V2.

I know I need a subquery to create rows based on server details in V2 where a rows does not exist for the KBID.

I'm making a right shambles of this; perhaps I need to go away and think on it.

 

[snips86x]

So, View3 is where I went to get to from the data contained in View1 and View2.

So unless you have more than 1 row of data for view 2, you won't get to view3. The join will only show you the records between 1 and 2 regardless of the join. You'll have to add each server in to each server group to get view3 as you've indicated above.

 

[darkgen]

So unless you have more than 1 row of data for view 2, you won't get to view3. The join will only show you the records between 1 and 2 regardless of the join. You'll have to add each server in to each server group to get view3 as you've indicated above.

Thanks for the message; I understand the confusion which is entirely my fault with my poor explanation. I basically need to produce a gridview showing a list of servers, grouped by the server group with headers showing the KBIDs within a table, so you can easily see where a KB patch has or has not been installed; where it has the cell returns the install date otherwise NULL / NA. This will require some code manipulation in VB / C# so before I get to that point I just wanted to produce a sql table with the data and then work it in excel.

As per the OP.
View1 returns a distinct list of all KBIDs
View2 returns rows of data containing server, server group, install date WHERE a KBID is installed.
I want a query which will create rows where a row does not exist in View2 (i.e. the KBID is not installed on that server). View3 was an example of the desired output.

I'm suspect this is a highly cack-handed approach!