I have 3 (or more) tables as follows in MySQL
Given the above table structure, I am looking to write a query that selects people who have selected "yes" to a set of options at during their visits.
E.g If the options to be queried are option_a = 'yes', option_b = 'no', option_c = 'no'; The query should return person with id 1 since he selected option_a as 'yes' at visit 1, option_b as 'no' in visits 1 and 2 and option_c as 'no' in visits 2 and 3. It should not return person 2 because person 2 selected option_a as 'yes' in visit 4, option_b as 'no' in visit 5 but never selected option c as no in any of their visits.
I have tried the following:
SELECT * FROM persons LEFT JOIN visits ON person.id = visit.person_id LEFT JOIN selected_options ON visit.id = selected_options.visit_id WHERE (option_a, option_b, option_c) IN (('yes', 'no', 'no'))
However it does not match any values.