CODEWITHSUNDEEP
sqloracle-database
Santi
Santi

Reputation: 451

Find out what group id contains all relevant attributes in SQL

So lets say in this case, the group that we have is groups of animals.

Lets say I have the following tables:

animal_id | attribute_id | animal
----------------------------------
 1        |  1           | dog   
 1        |  4           | dog
 2        |  1           | cat
 2        |  3           | cat
 3        |  2           | fish
 3        |  5           | fish


id | attribute
------------------
 1 | four legs 
 2 | no legs
 3 | feline
 4 | canine
 5 | aquatic

Where the first table contains the attributes that define an animal, and the second table keeps track of what each attribute is. Now lets say that we run a query on some data and get the following result table:

attribute_id 
------------
     1
     4

This data would describe a dog, since it is the only animal_id that has both attributes 1 and 4. I want to be able to somehow get the animal_id (which in this case would be 1) based on the third table, which is essentially a table that has already been generated that contains the attributes of an animal.

EDIT

So the third table that has 1 and 4 doesn't have to be 1 and 4. It could return 2 and 5 (for fish), or 1 and 3 (cat). We can assume that it's result will always match one animal completely, but we don't know which one.

Upvotes: 1

Views: 178

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269893

You can use group by and having:

with a as (
      select 1 as attribute_id from dual union all
      select 4 as attribute_id from dual
     )
select t.animal_id, t.animal
from t join
     a
     on t.attribute_id = a.attribute_id
group by t.animal_id, t.animal
having count(*) = (select count(*) from a);

The above will find all animals that have those attributes and any others. If you want animals that have exactly those 2 attributes:

with a as (
      select 1 as attribute_id from dual union all
      select 4 as attribute_id from dual
     )
select t.animal_id, t.animal
from t left join
     a
     on t.attribute_id = a.attribute_id
group by t.animal_id, t.animal
having count(*) = (select count(*) from a) and
       count(*) = count(a.attribute_id);

Upvotes: 2

Related Questions