SQL one-to-many relationship - How to SELECT rows depending on multiple to-many properties?

Question

A MySQL database contains two tables with a one-to-many relationship: One user can have many settings:

Users:
id    username    password
--------------------------
 1    Bob         123
 2    Alice       abc
 ...


Settings:
id   user_id   key     value
-----------------------------
 1   1         color   blue   // Bobs settings...
 2   1         theme   xy
 3   1         size    5
 4   2         size    5      // Alices settings...

Problem: How to find all users with color == blue AND size == 5?

Using a LEFT JOIN it is no problem to find users with one property:

SELECT users.id FROM users LEFT JOIN settings ON users.id = settings.user_id WHERE settings.key = 'color' AND settings.value = 'blue'

However, this does not work when searching for two settings at a time?

Is it possible to solve this with a single statement? What is the most efficient way to query this data?

Answer 1

One method uses aggregation and having:

select s.user_id
from settings s
where (key, value) in (  ('color', 'blue'), ('size', '5') )
group by s.user_id
having count(*) = 2;

This assumes that there are no duplicate settings (if so, you would need to use count(distinct)).