CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2008group-by
impossibru28
impossibru28

Reputation: 5

WHERE containing certain values, with CASE containing additional value

I'm trying to query a table named DeviceGroups that contains 2 columns, MachineID and GroupID. Here's an example

    MachineID GroupID
   ------------------
    1          A1
    1          A3
    2          A2
    3          A2
    3          A3
    4          A4
    4          A5
    5          A3

I need to show all MachineID's that contain GroupID A1 or A2, with a separate column that will give a Yes/No if that MachineID also contains A3. Here's the expected result:

MachineID GroupID ContainsA3?
------------------------------
1         A1      Yes
2         A2      No
3         A2      Yes

Notice MachineIDs 4 and 5 are not listed because they does not contain A1 or A2, even though MachineID 5 contains A3. I cannot create a WHERE clause to filter only A1 and A2 because all results will show ContainsA3? as false, and if I include A3 then MachineID 5 will appear with GroupID as NULL which is also not permitted. Any ideas? Here is where I got stuck:

SELECT MachineID,
CASE WHEN GroupID = 3 then "Yes" Else "No" END AS [ContainsA3?]
FROM DeviceGroups

And here's the result:

MachineID ContainsA3?
-----------------
1          No
1          Yes
2          No
3          No
3          Yes
4          No
4          No
5          Yes

If I add a WHERE clause:

SELECT MachineID,
CASE WHEN GroupID = 3 then "Yes" Else "No" END AS [ContainsA3?]
FROM DeviceGroups
WHERE GroupID = 3

MachineID ContainsA3?
-----------------
1          Yes
3          Yes
5          Yes

Upvotes: 0

Views: 49

Answers (4)

Chris
Chris

Reputation: 1702

create table #tbl ( 
mID int,
gID varchar(2) )    


insert into #tbl
values
(1, 'A1'),
(1, 'A3'),
(2, 'A2'),
(3, 'A2'),
(3, 'A3'),
(4, 'A4'),
(4, 'A5'),
(5, 'A3')


select a.*, case when b.gID is not null then 'Yes' else 'No' end as ContainsA3
from #tbl a
left join #tbl b
on b.mID = a.mID
and b.gID = 'A3'
where a.gID in ('A1','A2')



mID  gID    ContainsA3
1    A1     Yes
2    A2     No
3    A2     Yes

Upvotes: 0

Mohammed HIMMI
Mohammed HIMMI

Reputation: 39

From what I undertand a subquery will do the job :

SELECT MachineID, 
CASE WHEN GroupID in ('A1','A2') 
and 'A3'  in (select GroupID from DeviceGroups as dv 
              where DeviceGroups.MachineID = dv.MachineID) 
              then 'Yes' Else 'No' END AS [ContainsA3?] 
FROM DeviceGroups

Upvotes: 0

Fabio
Fabio

Reputation: 32445

You can use a sub query

SELECT groups.MachineID
, ISNULL(
    (SELECT 'Yes' FROM DeviceGroups 
         WHERE MachineID = groups.MachineID AND GroupID = 3)
    , 'No'
) AS [ContainsA3?]
FROM DeviceGroups groups
WHERE groups.GroupID IN (1, 2)
GROUP BY groups.MachineID

Upvotes: 2

GMB
GMB

Reputation: 222582

Consider:

select
    MachineID,
    max(case when GroupID in ('A1', 'A2') then GroupID end) GroupID,
    max(case when GroupID = 'A3' then 'Yes' else 'No' end) ContainsA3
from mytable
where GroupID in ('A1', 'A2', 'A3')
group by MachineID
having max(case when GroupID in ('A1', 'A2') then 1 end) = 1

The idea is to use the where clause to filter on groups A1, A2, and A3, aggregate by machine, and use the having clause to ensure that either A1 or A2 was seen. The rest is conditional aggregation in the select clause.

Upvotes: 0

Related Questions