CODEWITHSUNDEEP
sql-servert-sql
Akshay
Akshay

Reputation: 50

Need help on a SQL query

Here's the scenario:

I have 3 tables in a SQL Server 2008 database - SERVERS, InstalledPatches and Patchlist.

The SERVERS table has list of servers. InstalledPatches has list of servers and patches installed on them. Patchlist has list of all patches that SHOULD be installed on each server. All patches in PATCHLIST should be ideally installed on all servers in SERVERS table. I am trying to find the patches that are missing.

Sample data:

SERVERS

SERVERID     SERVERNAME
----------------------- 
     1        ABC
    ..        ..
  1500        XYZ

INSTALLEDPATCHES:

  SERVERID  PATCHID
  -----------------
       1        1
       1        2
       2        1
      ..       ..
    1500        1
    1500        2

PATCHLIST:

PATCHID   PATCHNUMBER
---------------------
     1    aaa
     2    bbb
     3    ccc
     4    ddd
    ..    ..
    15    ZZZ

Final report should indicate missing patches:

SERVERID   MissingPATCHID
-------------------------
     1           3
     1           4
     1        1500
     2           3
     2           4
     2        1500
   ..

I have tried to use below query, but cant find all missing patches for each server.

SELECT 
    A.* 
FROM 
    INSTALLEDPATCHES A 
RIGHT OUTER JOIN 
    PATCHLIST B ON A.PATCHID = B.PATCHID 
WHERE 
    A.PATCHID IS NULL

Any help would be really appreciated.

Thanks.

Upvotes: 2

Views: 62

Answers (2)

Erran Morad
Erran Morad

Reputation: 4743

Try my query. It works now.

select s.serverid, p1.patchid as MissingPatchID 
from [servers] as s
left join patchlist as p1
on 1=1
left join installedpatches as p2
on s.serverid = p2.serverid
and p1.patchid = p2.patchid
where p2.patchid is null

Upvotes: 0

mucio
mucio

Reputation: 7119

What about something like? 

   select s.SERVERID, 
          pl.PATCHID MissingPATCHID
       from SERVERS s
 cross join PATCHLIST pl
      where not exists (select SERVERID, 
                               PATCHID
                          from INSTALLEDPATCHES ip
                         where ip.SERVERID = s.SERVERID 
                           and ip.PATCHID = pl.PATCHID)

I just created this SQLFiddle demo.

Upvotes: 1

Related Questions