CODEWITHSUNDEEP
sqloracle-databaseselect
Miguel Ribeiro
Miguel Ribeiro

Reputation: 8184

Select values based on several lines and columns

I have this table:

id       characteristic      value
 1          color            blue
 1          shape            rectangle
 2          color            green
 2          shape            triangle

I need to select the id's based on color and shape, for instance, I want to select the ids which the color is blue and the shape is rectangle (it would return only the value 1).

The primary key is composite (id + characteristic) so no id will have more than one value for a characteristic.

Also, there is the possibility to have more or less characteristics.

Could anyone help me on this one?

Best regards

Upvotes: 0

Views: 170

Answers (2)

user330315
user330315

Reputation:

Another approach where you don't need to change too much if the number of conditions changes:

with conditions as (
   select 'color' as characterstic, 'blue' as value from dual
   union all 
   select 'shape' as characterstic, 'rectangle' as value from dual
)
select id
from characteristics c2
where (characteristic, value) in ( select characteristic, value from conditions )
group by id
having count(*) = (select count(*) from conditions)

That way you just need to add another "row" in the CTE and don't need to change anything else.

Upvotes: 2

Akhil
Akhil

Reputation: 7600

SELECT  id
  FROM  MyTable t
        JOIN MyTable t2 ON t.id = t2.id
 WHERE  t.characteristic  = 'color' AND t.value ='blue'
   AND  t2.characteristic = 'shape' AND t2.value='rectangle' --just fixed the t1 to t2

Upvotes: 2

Related Questions