sql - Mysql find parent records where given values are found in all child records -

sql - Mysql find parent records where given values are found in all child records -

i have 2 tables:

parent: +-----------+-----------+ | parent_id | | +-----------+-----------+ | 123 | x | | 231 | y | | 455 | z | +-----------+-----------+ relations: +-----+----------+--------------+--------------+ | id | parent_id| option_name | option_value | +-----+----------+--------------+--------------+ | 1 | 123 | colors | aaa | | 2 | 231 | colors | bbb | | 3 | 456 | colors | aaa | | 4 | 456 | country | ddd | +-----+----------+--------------+--------------+

what want do, take rows parent have options want in relations table:

eg:

select * parent p left bring together relations r on r.parent_id = p.parent_id option_name = colors , option_value = aaa , option_name = country , option_value = ddd

for above query row parent_id 455 returned, since has both options.

p.s: - above query wrong, gave illustration want - have more 2 options -> practically 1 unlimited

i think can done left bring together (for each alternative needed, add together different left bring together ... options table reach millions of records someday, , don't know how optimized query be)

you can utilize having:

select *, count(option_name) total parent p left bring together relations r on r.parent_id = p.parent_id (option_name = colors , option_value = aaa) or (option_name = country , option_value = ddd) having total > 1

you alter having status find number of options looking for

mysql sql foreign-keys