SQL - CASE with IN SELECT

Question

I am trying to isolate those records of [table] which only have specific (let's say bad) attributes. Bad attributes are those with ids 3, 6 and/or 7. To do that, I came up with the following solution:

SELECT * FROM (SELECT DISTINCT x, y, id_attribute FROM [table]) 
HAVING SUM(CASE WHEN id_attribute IN (3, 6, 7) THEN 0 ELSE 1 END) = 0

and this works fine. I would now like to take this one step further and remove the hardcoding on the (3, 6, 7) since the bad attributes might change. So, instead, I want to replace (3, 6, 7) with (SELECT DISTINCT id_attribute from [other_table] where description = 'bad').

But, when I put the two together, my query fails with the message:

Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

Is there another way to do this?

Answer 1

You can use Left Join for isolating those bad attributes:

SELECT * FROM (
SELECT DISTINCT x, y, id_attribute,other_table.id as other_id_attribute FROM [table]
left join other_table on id_attribute=other_table.id
) 
GROUP by ....
HAVING SUM(CASE WHEN other_id_attribute is not null THEN 0 ELSE 1 END) = 0

Answer 2

Maybe you can try something like this:

SELECT DISTINCT x, y, t.id_attribute 
FROM [table] t
WHERE t.id_attribute IN (
    SELECT ot.id_attribute 
    FROM [other_table] ot 
    WHERE ot.description = 'bad')