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.