TSQL and joining 2 views to create rows where no relationship

darkgen · 22 Jan 2020 at 14:00

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

Code:

KBID
KB1
KB2
KB3
KB4
KB5

Code:

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

Code:

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 · 22 Jan 2020 at 14:04

Whats the issue isnt it just left outer joins?

glenimp617 · 22 Jan 2020 at 14:12

Cant you just do this in SCCM?

darkgen · 22 Jan 2020 at 14:15

No, that just creates additional KBID rows with NULLS.

Code:

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:

Code:

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

darkgen · 22 Jan 2020 at 14:17

TheOracle said:
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 · 22 Jan 2020 at 14:20

darkgen said:
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 · 22 Jan 2020 at 14:29

The nulls = your N/A

darkgen · 22 Jan 2020 at 14:30

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 · 22 Jan 2020 at 14:33

chroniclard said:
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 · 22 Jan 2020 at 14:38

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 · 22 Jan 2020 at 14:39

^^ Same result as left outer join

darkgen · 22 Jan 2020 at 14:40

snips86x said:
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 · 22 Jan 2020 at 14:41

darkgen said:
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 · 22 Jan 2020 at 14:43

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

snips86x · 22 Jan 2020 at 14:43

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

Might be the join he's done or mock up

darkgen · 22 Jan 2020 at 14:46

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

chroniclard · 22 Jan 2020 at 14:48

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

darkgen · 22 Jan 2020 at 14:50

chroniclard said:
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 · 22 Jan 2020 at 14:51

darkgen said:
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 · 22 Jan 2020 at 17:15

snips86x said:
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!