HiveBrain v1.2.0
Get Started
← Back to all entries
patternMinor

SQL Query question (items not having some value in a column)

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
columnsqlqueryhavingvalueitemssomequestionnot

Problem

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) bu

Solution

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:

Code Snippets

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')

Context

StackExchange Database Administrators Q#207488, answer score: 2

Revisions (0)

No revisions yet.