MySQL GROUP BY…HAVING different values same field

Question

I want to use a query similar to the following to retrieve all rows in events that have at least one corresponding event_attendances row for 'male' and 'female'. The below query returns no rows (where there certainly are some events that have event_attendances from both genders).

Is there a way to do this without a subquery (due to the way the SQL is being generated in my application, a subquery would be considerably more difficult for me to implement)?

SELECT * FROM events e
LEFT JOIN event_attendances ea ON (e.id = ea.event_id)
GROUP BY e.id
HAVING ea.gender = 'female' AND ea.gender = 'male'

Answer 1

HAVING generally used with aggregate functions.

You should do self-jointo get the desired results, since ea.gender = 'female' AND ea.gender = 'male' is contradictory,which always returns empty set.

You can try this

SELECT T1.* 
FROM events T1 
INNER JOIN 
(SELECT events.id 
    FROM events
    LEFT JOIN event_attendances ON (events.id = event_attendances.event_id)
    GROUP BY events.id
    HAVING COUNT(DISTINCT event_attendances.gender) = 2) T2 ON T1.events.id=T1.events.id

Hope this helps.

Answer 2

Use

HAVING sum(ea.gender = 'female') > 0 
   AND sum(ea.gender = 'male') > 0

or

HAVING count(distinct ea.gender) = 2

BTW you should use a subquery to get all data when you group.

SELECT * 
FROM events
where id in
(
    SELECT events.id 
    FROM events
    LEFT JOIN event_attendances ON (events.id = event_attendances.event_id)
    GROUP BY events.id
    HAVING count(distinct event_attendances.gender) = 2
)