SQL SELECT criteria in another table

Question

I have 2 related tables:

messages
--------

mid subject
--- -----------------
1   Hello world
2   Bye world
3   The third message
4   Last one


properties
----------

pid mid name             value
--- --- ---------------- ----------- 
1   1   read             false
2   1   importance       high
3   2   read             false
4   2   importance       low
5   3   read             true
6   3   importance       low
7   4   read             false
8   4   importance       high

And I need to get from messages using the criteria on the properties table. Eg: if I have a criteria like return unread (read=false) high prio (importance=high) messages it should return

mid subject
--- -----------------
1   Hello world
4   Last one

How could I get this with a SELECT clause (MySQL dialect)?

Answer 1

In SQL, any expression in a WHERE clause can only reference one row at a time. So you need some way of getting multiple rows from your properties table onto one row of result. You do this with self-joins:

SELECT ...
FROM messages AS m
JOIN properties AS pRead 
    ON m.mid = pRead.mid AND pRead.name = 'read'
JOIN properties AS pImportance 
    ON m.mid = pImportance.mid AND pImportance.name = 'importance'
WHERE pRead.value = 'false' AND pImportance.value = 'high';

This shows how awkward it is to use the EAV antipattern. Compare with using conventional attributes, where one attribute belongs in one column:

SELECT ...
FROM messages AS m
WHERE m.read = 'false' AND m.importance = 'high';

By the way, both answers from @Abe Miessler and @Thomas match more mid's than you want. They match all mid's where read=false OR where importance=high. You need to combine these properties with the equivalent of AND.

Answer 2

I believe the query below will work.
UPDATE: @Gratzy is right, this query won't work, take a look at the structure changes I suggested.

SELECT DISTINCT m.id as mid, m.subject
FROM message as m
INNER JOIN properties as p
ON m.mid = p.mid
where (p.name = 'read' and p.value = 'false') or (p.name = 'importance' AND p.value = 'high')

The structure of your properties table seems a little off to me though...

Would it be possible to structure the table like this:

messages
--------

mid subject           Read      Importance
--- ----------------- --------- ------------
1   Hello world       false     3
2   Bye world         false     1
3   The third message true      1
4   Last one          false     3

importance
----------

iid importanceName
--- --------------
1   low
2   medium
3   high

and use this query:

SELECT m.id as mid, m.subject
FROM message as m
where m.read = false AND m.importance = 3