Menu
I'm having some troubles with a MySQL Query.
I want to select all the elements from a table, grouping from a certain column, BUT only retrieve those where ALL the elements in the group matches the WHERE clause.
For example, I have a table called "pets" with 3 columns:
Id| animal | name
1 | dog | marge
2 | dog | homer
3 | cat | marge
4 | cat | lenny
5 | rabbit | homer
5 | rabbit | carl
6 | rabbit | marge
And I want to pick all the animals where ALL his group members have name IN('homer','bart','marge','lisa','maggie')
If you can't understand my question, let me know. Thanks!
483
SQL GROUP BY Syntax “GROUP BY column_name1” is the clause that performs the grouping based on column_name1. “[,column_name2,…]” is optional; represents other column names when the grouping is done on more than one column. “[HAVING condition]” is optional; it is used to restrict the rows affected by the GROUP BY clause.
We know that WHERE clause is used to place conditions on columns but what if we want to place conditions on groups? This is where HAVING clause comes into use. We can use HAVING clause to place conditions to decide which group will be the part of final result-set.
You can use a SELECT command with a GROUP BY clause to group all rows that have identical values in a specified column or combination of columns, into a single row.
COUNT(*) returns the number of rows in a specified table, and it preserves duplicate rows. It counts each row separately. This includes rows that contain null values.
Give this a shot. Let me know if I'm missing the mark on what exactly it is you're looking for.
select animal
from pets
group by animal
having animal not in
(
select animal
from pets
where name not in ('homer','bart','marge','lisa','maggie')
group by animal
)
It sounds obvious when you say it, but the trick to getting only the results you want is sometimes to first create a query that gets all the results you don't want and then just exclude them.
143
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
