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

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 6 // Alices settings...


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

color == blue AND size == 5

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

LEFT JOIN

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?

2 Answers
2

One method uses aggregation and having:

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)).

count(distinct)

You can use an OR condition in the join.

SELECT
users.id
FROM
users
LEFT JOIN
settings ON users.id = settings.user_id
WHERE
(settings.key = 'color' AND settings.value = 'blue')
OR
(settings.key = 'size' AND settings.value = '5');


By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.