CODEWITHSUNDEEP
sqldb2distinctunique-constraint
Kartik
Kartik

Reputation: 3

SQL DB2 - Select query based on a column value unique to another column

I have a table with following fields:

ID    |   Attribute_ID
200          3
200          4
200          6
200          7
201          6
202          2
202          6
203          6
204          2
204          4
204          6

I want my result to be like this:

ID    |   Attribute_ID
201          6
203          6

Basically, I need to write a query that has 'ID' associated with 'Attribute_ID' = 6 and not with 'Attribute_ID' = 1,2,3. That is a column value unique to another column value, if that makes any sense.

I am using DB2.

Below is my attempt, but I am sure I am doing it wrong way.

SELECT qa.ID, qa.ATTRIBUTE_ID
    FROM QUESTION_ATTRIBUTES qa
    WHERE(
            (qa.ID,qa.ATTRIBUTE_ID) IN (Values (qa.ID,4),(qa.ID,6),(qa.ID,7))
        AND (qa.ID,qa.ATTRIBUTE_ID) NOT IN (Values (qa.ID,1),(qa.ID,2),(qa.ID,3))            
        )

Thanks for any suggestions.

Upvotes: 0

Views: 1358

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271023

You can approach this with aggregation and a having clause:

select qa.id
from question_attributes
group by qa.id
having min(Attribute_ID) = 6 and max(Attribute_ID) = 6;

Upvotes: 2

Related Questions