CODEWITHSUNDEEP
mysqlsearch
acme
acme

Reputation: 14866

MySQL: Searching a single table multiple times?

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

Answers (3)

John Boker
John Boker

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

Eric
Eric

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

John Kugelman
John Kugelman

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

Related Questions