6

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...

6
  • 1
    Hi Martin -- I think it'd be easier for someone to answer if you provided the data as CREATE TABLE/INSERT, and the expected results as fabricated query output. Word problems are a lot tougher to solve. – Erik Darling May 22 '18 at 14:08
  • Would also help if you provided what version of DB server you are using. – MrTCS May 22 '18 at 14:12
  • I liked the table example of the data inside it, but if we could also have an example of what the results should be too with the table of the data, that would help. – Shaulinator May 22 '18 at 14:16
  • Hi Eric! (et. all) Thanks for looking into this, I'm going to amend my original question with a CREATE TABLE/ INSERT so you can try it at home! I know with SQL everything is possible but finding a solution to this one requires some T-SQL mastery I don't have... – Martin Surasky May 22 '18 at 14:58
  • @MartinSurasky thanks -- can you add expected results? – Erik Darling May 22 '18 at 15:09
2

I believe the following query will give you the right computer name

 select ComputerName from dbo.computers
 where os= 'windows 7'
 except
 select ComputerName from dbo.computers
 where os= 'windows 7'
 and appname not in (select appname from dbo.computers where os='windows 10')

The result is:

enter image description here

1
  • Simple... yet brilliant! This is a good example of why it is important to remember the practical usages of T-SQL constructs that are not so commonly used (such as EXCEPT or INTERSECT) – Martin Surasky May 22 '18 at 15:55
0

Another approach

DECLARE @t TABLE (ComputerName varchar(255), OS varchar(255), AppName varchar(255), AppVersion varchar(10))

 INSERT INTO @t 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') 

 declare @a table (AppName varchar(255) primary key);
 insert into @a 
 select distinct AppName
   from @t 
  where OS = 'Windows 10' ;
 select * from @a;

 select t7.ComputerName
   from @t t7 
   left join @a a 
     on t7.AppName = a.AppName 
  where t7.OS = 'Windows 7'
  group by t7.ComputerName 
  having count(*) = count(a.AppName);

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.