Menu
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'
339
Yes, it is possible to use MySQL GROUP BY clause with multiple columns just as we can use MySQL DISTINCT clause.
The SQL GROUP BY Statement The GROUP BY statement groups rows that have the same values into summary rows, like "find the number of customers in each country". The GROUP BY statement is often used with aggregate functions ( COUNT() , MAX() , MIN() , SUM() , AVG() ) to group the result-set by one or more columns.
Using Group By and Order By Together When combining the Group By and Order By clauses, it is important to bear in mind that, in terms of placement within a SELECT statement: The GROUP BY clause is placed after the WHERE clause. The GROUP BY clause is placed before the ORDER BY clause.
We can use the group by multiple-column technique to group multiple records into a single record.
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
)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
