Select rows from table using conditions across multiple rows in left joined table
Another approach (Bits and Booleans)
If the &quot;options&quot; are exactly yes/no (not &quot;unknown&quot; or &quot;maybe&quot;), then you could use a &quot;bit&quot; and put those bits into a SET or INT.  This would lead to a much more compact dataset, though the WHERE code would be messier.
INT would allow Boolean operations with which you could compute things that you seem to need:

OptionA was &quot;yes&quot; for any visit by person-1.
OptionA was &quot;yes&quot; for all visits by person-1.

options BIGINT UNSIGNED lets you handle 64 options, no more.  You could (with added complexity) use more than one column if you need more than 64 options.
The gist of the code is to GROUP BY person and do BIT_AND(options) to combine every column in order to test for &quot;yes&quot; at all visits.  BIT_OR would give you yes at any visit.  Other expressions would handle any/all &quot;no&quot;.
Masking would let you select the, say, 3 options that the query is interested in.
To find visits with a particular set of yes/no values, just masking and equality suffices.  So (option_a, option_b, option_c) IN (('yes', 'no', 'no')) maps to (assuming a is bit 0, b is bit 1, etc):
WHERE options
    &amp; ((1&lt;&lt;0) | (1&lt;&lt;1) | (1&lt;&lt;2))  -- check bits 0,1,2
    =  (1&lt;&lt;0)                     -- for 0 being YES; the rest being NO

Back to what might be the desired query:
WHERE BIT_OR(options) &amp; (1&lt;&lt;0)  -- option_a is YES for _any_ visit
  AND BIT_AND(options) &amp; (1&lt;&lt;1) = 0  -- b was NO for _any_ visit
  AND BIT_AND(options) &amp; (1&lt;&lt;2) = 0  -- c was NO for _any_ visit

Tips
LEFT seems to be 'wrong', use a regular JOIN.
&quot;Row constructors&quot;, in the past (pre 5.7.3), were quite inefficient.  Rewrite the WHERE if you are running an old version.
Please qualify column names so we know which table(s) they are in.
You show 4 options now.  Might there be 5 tomorrow?  And 6 the day after?  This leads to a maintenance question for the schema.