Link to home
Start Free TrialLog in
Avatar of Jeffrey Renfroe
Jeffrey RenfroeFlag for United States of America

asked on

MS SQL Query to look for Specific Value with Exclusions

Hello. I have an MS SQL 2005 database. I have a view that I want to get results from. In this view, I want to query for machines with a specific value in column and only if that is the only value. My problem is that the value can be present as well as other values. For example, I want to output all packages that are configured to only run on Windows XP machines. Some packages are configured to run on Windows XP and Windows 7 but I do not want those. What SQL syntax do I use to exclude the packages that are configured to run on both?

The below query I created outputs the XP only packages but also the Windows 7 and XP ones.

SELECT DISTINCT PackageID, MinVersion, MaxVersion
FROM v_OS_Details
WHERE (MinVersion = '5.10.0000.0) and (MaxVersion = '5.10.9999.9999')

How would I get the above to output a package if (MinVersion = '5.10.0000.0) and (MaxVersion = '5.10.9999.9999') are it's only values for MinVersion and MaxVersion?

Thank you for the assistance.
Avatar of tigin44
tigin44
Flag of Türkiye image

what field do you use to identify the supported operating systems except osVersions?
As I remember the version number for the Windows 7 is 6.1 and for Windows Vista 6.0
 
so your query should be like this


SELECT DISTINCT PackageID, MinVersion, MaxVersion
FROM v_OS_Details
WHERE (MinVersion = '5.10.0000.0)
Avatar of Jeffrey Renfroe

ASKER

Thank you for the response. You are correct about the version number of Win7 and Vista. However, your query has the same problem has mine.

When I run your query or mine, I get results like the below.

Package 1
Package 2

However, Package 2 is configured to run on XP and Windows 7. I do not want the output to include packages like this. I only want it to output packages like Package1 which is configured to only run on Windows XP.
this should work

SELECT DISTINCT PackageID, MinVersion, MaxVersion
FROM v_OS_Details
WHERE MinVersion = '5.10.0000.0
  and MaxVersion = '5.10.0000.0)
That is the same query that I have.
I don't think that is the same as yours, however what query are you using and what values is it returning for minVersion and MaxVersion and can you tell us why the values you see are not what you want?
Hopefully, this is a better example.

Package A - MinVersion 5.10.0000.0 and MaxVersion 5.10.9999.9999'

Package B - MinVersion 5.10.0000.0 and MaxVersion 5.10.9999.9999'
                     MinVersion 6.10.0000.0 and MaxVersion 6.10.9999.9999'


My query below sees that both package A and Package B have MinVersion 5.10.0000.0 and MaxVersion 5.10.9999.9999' but I want it to ignore Package B since it also have a MinVersion 6.10.0000.0 and MaxVersion 6.10.9999.9999' and not output that into my results.

SELECT DISTINCT PackageID, MinVersion, MaxVersion
FROM v_OS_Details
WHERE (MinVersion = '5.10.0000.0) and (MaxVersion = '5.10.9999.9999')
ASKER CERTIFIED SOLUTION
Avatar of ralmada
ralmada
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
This is what I was looking for. Thank you for the assistance.
Thank you for the assistance.
Avatar of awking00
Can you provide some sample data for v_OS_Details and your expected results so we have something to test with?