mysql statement rows_MySQL condition statement across multiple rows

There are several query patterns that will return a result that satisfies the specification.

We can use NOT EXISTS with a correlated subquery:

SELECT p.people

FROM profile p

WHERE p.favorite = 'Movie'

AND NOT EXISTS ( SELECT 1

FROM profile q

WHERE q.favorite = 'Jogging'

AND q.people = p.people /* related to row in out query */

)

ORDER

BY p.people

An equivalent result can also be done with an anti-join pattern:

SELECT p.people

FROM profile p

LEFT

JOIN profile q

ON q.people = p.people

AND q.favorite = 'Jogging'

WHERE q.people IS NULL

AND p.favorite = 'Movie'

ORDER BY p.people

Another option is conditional aggregation. Without a guarantee about uniqueness, and some MySQL shorthand:

SELECT p.people

FROM profile p

GROUP

BY p.people

HAVING 1 = MAX(p.favorite='Movie')

AND 0 = MAX(p.favorite='Jogging')

A more portable more ANSI standard compliant syntax for the conditional aggregation:

SELECT p.people

FROM profile p

GROUP

BY p.people

HAVING 1 = MAX(CASE p.favorite WHEN 'Movie' THEN 1 ELSE 0 END)

AND 0 = MAX(CASE p.favorite WHEN Jogging' THEN 1 ELSE 0 END)