patternMinor
SQL Query question (items not having some value in a column)
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...)
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")
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
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.0The 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
The result is:
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.