Need help on a SQL query

Akshay Published at Dev

Akshay

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.

mucio

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.

Collected from the Internet

Please contact [email protected] to delete if infringement.