T-sql – SQL Query question (items not having some value in a column)

Here is a conundrum I need to solve with some T-SQL Magic, I tried and failed.

It's one of these things that, before I think of the actual solution I think I can solve in 3 minutes with some T-SQL, but then when the "rubber meets the road" I just can't picture in my mind the right operators to achieve this logic.

I'm wondering if anyone in the Stack Exchange community can find an elegant solution to this one. I guess with a combination of 3 or 4 queries using temp tables or cursors I could find the solution, but it would not be SET based…

I have a table that looks like this (more or less…)

CREATE TABLE Computers (
   ID               int IDENTITY(1,1),
   ComputerName     varchar(255),
   OS               varchar(255),
   AppName          varchar(255),
   AppVersion       varchar(10)
)

So, every row has a computer name, an OS value (that can be either Windows 7 OR Windows 10) and then a description of an application (e.g. AppName = "Notepad++", AppVersion = "7.5.6")

ComputerName   | OS           | AppName   | AppVersion
Computer 1     | Windows 7    | App 1     | 1.0
Computer 1     | Windows 7    | App 2     | 1.0
Computer 1     | Windows 7    | App 3     | 1.0
Computer 2     | Windows 10   | App 4     | 1.0
Computer 2     | Windows 10   | App 5     | 1.0
Computer 3     | Windows 10   | App 4     | 1.0
Computer 4     | Windows 7    | App 4     | 1.0
Computer 4     | Windows 7    | App 5     | 1.0
Computer 5     | Windows 7    | App 1     | 1.0
Computer 5     | Windows 7    | App 4     | 1.0
Computer 5     | Windows 7    | App 5     | 1.0

The way it works is like this: if a computer has 10 apps installed, then you will have 10 rows in this table (one for each app). The computername and OS will be repeated (please, don't start a discussion about 2nd normal form… this is strictly a question about a query to solve a problem).

So, the problem I need to solve is: I need to find a list of computers WHERE the OS is Windows 7 (that's EAAAASY) but…

Those computers should only show up in my list if ALL the applications that are reported in this table for that PC are reported as being used on other Windows 10 computers.

If a Windows 7 computer has apps that are not listed in a Windows 10 computer, then they should not show up in this list. Each and every app for a computer must comply with this rule for the computer to qualify on my list.

The idea is to know that the computers I'm getting in my SELECT have all apps that I know work for Windows 10, so they should be safer to migrate.

Sample:

For those trying this at home, here is a CREATE TABLE and some INSERTs you have the right idea of what my data looks like

-- Create the table
 CREATE TABLE Computers (
    ComputerName    varchar(255),
    OS              varchar(255),
    AppName         varchar(255),
    AppVersion      varchar(10)
 )

 -- Insert some values...
 INSERT INTO Computers
 VALUES 
 ('Computer 1', 'Windows 7', 'App 1', '1.0'),
 ('Computer 1', 'Windows 7', 'App 2', '1.0'),
 ('Computer 1', 'Windows 7', 'App 3', '1.0'),
 ('Computer 2', 'Windows 10', 'App 4', '1.0'),
 ('Computer 2', 'Windows 10', 'App 5', '1.0'),
 ('Computer 3', 'Windows 10', 'App 4', '1.0'),
 ('Computer 4', 'Windows 7', 'App 4', '1.0'),
 ('Computer 4', 'Windows 7', 'App 5', '1.0'),
 ('Computer 5', 'Windows 7', 'App 1', '1.0'),
 ('Computer 5', 'Windows 7', 'App 4', '1.0'),
 ('Computer 5', 'Windows 7', 'App 5', '1.0')

• With this example, the only computer that qualifies for my query would be Computer 4, as is the only Windows 7 computer where ALL apps associated with it (Apps 4 and 5) also exist in at least one row for a Windows 10 PC.

• Computer 5 does not qualify because one of the apps associated with it ("App 1") only exists in this set on another Windows 7 computer (Computer 1) so I can't say that this app is already working on a Windows 10 PC.

• Computers 2, and 3 are Windows 10. So that should be enough to discard them….

• Computer 1 should also NOT qualify, all their apps are not running in Windows 10 PCs.

Hopefully makes sense…