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'

pls give structurew for event_attendances – Subin Chalil Nov 11 '15 at 09:56 — Subin Chalil, Nov 11 '15 at 09:56

juergen d · Accepted Answer · 2018-09-14T11:58:05.697

14

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
)

edited Sep 14 '18 at 11:58

answered Nov 11 '15 at 09:50

juergen d

201,996
37
293
362

You might mention the failed assumption that a group by will show more than one value for a single field. – Samuel Åslund Nov 11 '15 at 09:52
@SamuelÅslund Actually I am not assuming that - I am not interested in the data in the associated event_attendances records. – Will Nov 11 '15 at 09:54
@Will: What he means is that MySQL has an awful feature to allow selecting columns you don't group or aggregate. Don't do this. Use the subquery I showed. – juergen d Nov 11 '15 at 09:55
@juergend Thanks, this solution works well. Not sure I completely understand why a subquery is necessary here though, what would be the difference between the example you gave and my query (amended to use `SUM(ea.gender...`? – Will Nov 11 '15 at 10:28
@Will: Try to understand how grouping works. You don't look at a single record. You build a group and look at multiple records. Then you select the columns that built the group by your definition. Other columns need to be aggregated like summed or counted or whatever. If you don't do that then the DB does not know how to pick the records. Any other DB engine throws an error then. But MySQL allows that. – juergen d Nov 11 '15 at 13:33
@Will Your "HAVING" clause refers to the grouped result, thus the ea.gender can have only one value for each row. The having clause Juergen writes gets around this by virtually adding a "count" column to the result row. – Samuel Åslund Nov 12 '15 at 17:18

Subin Chalil · Answer 2 · 2015-11-11T10:08:57.097

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.

MySQL GROUP BY...HAVING different values same field

2 Answers2

Linked