CODEWITHSUNDEEP
sqloracle-databaseselect
miracle_the_V
miracle_the_V

Reputation: 1166

Select given combination of values in one column

I have a trouble writing this script in a given way.
Let's say we have a table with two columns: entity and attribute.
attribute has a static number of possible values, say 1 to 10. One entity can only have 1 row for each attribute value (entity a can't have attribute 8 two times).
How do I select only those entities, which only have attribute values 2 and 6 (both of them)?

Upvotes: 0

Views: 425

Answers (1)

sstan
sstan

Reputation: 36553

I hope I understood your statement correctly:

select t1.entity
from your_table t1
join your_table t2
  on t1.entity = t2.entity
 and t2.attribute = 6
where t1.attribute = 2
and not exists (
    select null
    from your_table t3
    where t3.entity = t1.entity
    and t3.attribute not in (2,6)
);

The above not only makes sure to only give you entities that have both 2 and 6 attributes, but it also makes sure it doesn't have any other attributes besides those 2. That's what I understood from your problem statement.

EDIT

Here is another, simpler way that relies on the fact that you cannot have duplicate attribute entries.

select entity
from your_table
where attribute in (2,6)
group by entity
having count(*) = 2;

If you need to check 3 attributes, just adjust the IN clause to add your 3rd value, and change the having count(*) clause to check against 3 instead of 2.

Upvotes: 2

Related Questions