MySQL GROUP BY...HAVING different values same field

mysql join group-by having
Will picture Will · Nov 11, 2015 · Viewed 7.7k times · Source

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

juergen d picture juergen d · Nov 11, 2015

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
)

Related questions

How to get multiple counts with one SQL query?

I am wondering how to write this query. I know this actual syntax is bogus, but it will help you understand what I am wanting. I need it in this format, because it is part of a much bigger query. …

Read More
LEFT JOIN after GROUP BY?

I have a table of "Songs", "Songs_Tags" (relating songs with tags) and "Songs_Votes" (relating songs with boolean like/dislike). I need to retrieve the songs with a GROUP_CONCAT() of its tags and also the number of likes (…

Read More
Mysql UNION and GROUP BY

I have 2 tables I need to add together based on a date and 2 values. This gives me the list of all information - fine. $query = (SELECT date, debit, credit , note FROM proj3_cash ) UNION (SELECT settle, purch, sale, issue FROM …

Read More