Searching for entities in a EAV structured data model

Question

I'm trying to search in a database that uses an entity-attribute-value model to store objects. Could someone tell me what is wrong with the query below?

SELECT * FROM object a
   INNER JOIN object_meta b
   ON a.id = b.object 
WHERE
   a.name LIKE '%michael%' AND
   b.name='type' AND b.value='judge'
   AND ( 
      (b.name='country' AND b.value='France') 
      OR  
      (b.name='country' AND b.value='Sweden')
   ) 
ORDER BY a.name DESC
LIMIT 0, 50;

In this case I want to select objects which name contains "michael" and that is of type judge and comes from either Sweden or France. I can see that it exists objects that matches the query when browsing the database but nothing comes out when I run the query. I'm running the query via command line so it has nothing to do with application code.

Table structure

object
+----+------------+
| id | name       |
+----+------------+
|  1 | Michael... |
|  2 | Michael... | 
+----+------------+

object_meta
+--------+---------+-----------+
| object | name    | value     |
+--------+---------+-----------+
|   1    | type    | judge     |
|   2    | type    | judge     |
+--------+---------------------+

Answer 1

Your query returns an empty set because there is no row in the JOIN that can satisfy both conditions b.name='type' AND b.name='country'

Try this:

SELECT * FROM object a
   INNER JOIN object_meta b
   ON a.id = b.object 
WHERE
   a.name LIKE '%michael%' AND
   b.name='type' AND b.value='judge'
   AND EXISTS( SELECT 1 FROM object_meta b1 
               WHERE b1.[object]=b.[object] AND ( b1.name='country' AND (b1.value='France' OR b1.value='Sweden'))

   ) 
ORDER BY a.name DESC
LIMIT 0, 50;