0

I have a table with the following data (simplified here):

ID | PolicyName | Setting | State | Value

1 Default DiskUsage Enabled 1
2 Default Memory Enabled 1
3 Default CPU Enabled 0
4 Personal DiskUsage Enabled 0
5 Personal Memory Enabled 1
6 Personal CPU Enabled 0
7 Custom DiskUsage Enabled 1
8 Custom Memory Enabled 0
9 Custom CPU Enabled 1

I need to run a query looking for policy names that have Setting of DiskUsage Enabled and set to 1 along with Memory Enabled and set to 1. So it should return:

PolicyName

Default

Not sure how I can go about this one.

1 Answer 1

0

Assuming the data is constrained to avoid duplicates, how about something like this? This is assuming Microsoft SQL Server syntax.

SELECT United.PolicyName FROM
(SELECT PolicyName FROM dbo.Policies 
WHERE Setting = 'DiskUsage'
  AND State = 'Enabled'
  AND Value = 1
UNION ALL
SELECT PolicyName FROM dbo.Policies 
WHERE Setting = 'Memory'
  AND State = 'Enabled'
  AND Value = 1
) AS United
GROUP BY United.PolicyName
HAVING COUNT(*) = 2

RESULTS: Default

This specifically queries for each of the states that you care about. Since there are two requirements, then the UNION will produce two rows when both selects are find a row.

1
  • Thanks a bunch! This really helps out. I'll look into UNION more as required.
    – techguy817
    Commented Oct 22, 2014 at 15:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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