Query to select distinct name based on several factors

Question

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.

RLF · Accepted Answer · 2014-10-22T12:38:18.040

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.

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

Query to select distinct name based on several factors

ID | PolicyName | Setting | State | Value

PolicyName

1 Answers1