Reputation: 14866
I have a table that's holding metadata for entrys from another table. The metadata-table looks like this (I removed relating Ids so it's more obvious):
id entry_id property value
1 12 color blue
2 12 shape circle
3 13 color red
4 13 shape circle
5 14 color blue
6 14 shape square
7 15 material stone
8 12 material plastic
Now I want to search this table for the properties, like choosing all entries where color is blue:
select entry_id from table where property = 'color' and value = 'blue'
So far, so good. But how do I expand the query when I have multiple conditions? For example I want to search for all entries where color is blue and shape is circle. Right now I'd achieve this with unions:
select entry_id from table where property = 'color' and value = 'blue'
union
select entry_id from table where property = 'shape' and value = 'circle'
this obviously gets ugly the more properties I want to look for. And I think it's not very fast, too. Is there are more elegant way to do this? The reason for this table is, that I have objects with metadata which can be set by the user.
Thank you!
Upvotes: 1
Views: 813
Reputation: 83729
is this what you're looking for?
select
distinct e1.entry_id
from
table e1
inner join
table e2 on e1.entry_id=e2.entry_id
where
e1.property='color' and
e1.value='blue' and
e2.property='shape' and
e2.value='circle'
Upvotes: 1
Reputation: 95243
You can trick MySQL into building a hash table in lieu of union
or or
:
select distinct
entry_id
from
table t
inner join
(select 'shape' as property, 'circle' as value
union all
select 'color' as property, 'blue' as value) s on
t.property = s.property
and t.value = s.value
You can also try an exists
:
select distinct
entry_id
from
table t
where
exists (select 1 from
(select 'shape' as property, 'circle' as value
union all
select 'color' as property, 'blue' as value) s
where
s.property = t.property
and s.value = t.value)
Using one of these two methods, you can append as many search conditions as your little heart desires by simply tacking on another union all
to the s
subquery.
Upvotes: 0
Reputation: 362187
SELECT a.entry_id
FROM table a INNER JOIN table b USING (entry_id)
WHERE a.property = 'color' AND a.value = 'blue'
AND b.property = 'shape' AND b.value = 'circle'
Upvotes: 0