MySQL: Select group if any row matches

Question

Suppose I have a table like this:

+----+----------+
|name|testValue |
+----+----------+
|A   |3         |
+----+----------+
|A   |4         |
+----+----------+
|A   |7         |
+----+----------+
|B   |0         |
+----+----------+
|B   |3         |
+----+----------+
|C   |5         |
+----+----------+
|C   |5         |
+----+----------+
|C   |6         |
+----+----------+

Now I first want to group the columns by name, and then be able to only retrieve the groups where any of the rows meet a certain condition. For every group, I want to retrieve all the "testValues", which I am currently doing with a GROUP_CONCAT.

For example, if I want to retrieve all the groups where any of the "testValues" are between 4 and 8:

SELECT
    name,
    testValue,
    GROUP_CONCAT(testValue SEPARATOR '#') AS testValues
FROM myTable 
GROUP BY name 
HAVING testValue > 4 AND testValue < 8

But this only checks if the "testValue" of the first row in the groups matches. My expected output in this example would be:

+----+---------+----------+
|name|testValue|testValues|
+----+---------+----------+
|A   |3        |3#4#7     |
+----+---------+----------+
|C   |5        |5#5#6     |
+----+---------+----------+

While the actual output of my example query is:

+----+---------+----------+
|name|testValue|testValues|
+----+---------+----------+
|C   |5        |5#5#6     |
+----+---------+----------+

My questions: 1. How can I make it so that it checks if any of the rows matches, not only the first? Am I even supposed to use HAVING for this?

2. And also is there any better way to return all values from the rows in a group than using GROUP_CONCAT?

Btw, I've tried to google this, but found it quite difficult.

Answer 1

You can use:

SELECT name, MIN(testValue) AS testValue, 
       GROUP_CONCAT(testValue SEPARATOR '#') AS testValues
FROM mytable
GROUP BY name
HAVING COUNT(CASE WHEN testValue BETWEEN 4 AND 8 THEN 1 END) > 0 

Demo here

HAVING clause is using conditional aggregation to count the number of testValue values that fall within [4-8] range. This is applied separately to each name group.

Only groups that satisfy the HAVING predicate are returned by the query. Hence, only groups having at least one row with testValue in [4-8] range are returned.

Note: It is not clear which value you want returned as testValue. In the sample result set provided in the OP the minimum value is chosen. If you want the actual values that fall within the [4-8] range, then you can use:

GROUP_CONCAT(CASE 
               WHEN testValue BETWEEN 4 AND 8 
               THEN testValue 
             END SEPARATOR '#') AS testValue

in the SELECT clause of your query.

Answer 2

You are using a partial-group-by hence the "matches only first row in the group" issue.

You could use an inner query to find all names where condition matches. Then lookup those names in the outer query. With proper indexes this approach could outperform IN and HAVING solutions.

SELECT mytable.name, GROUP_CONCAT(testValue SEPARATOR '#') AS testValues
FROM mytable
INNER JOIN (
  SELECT DISTINCT name
  FROM mytable
  WHERE testValue BETWEEN 4 AND 8
) AS subquery ON mytable.name = subquery.name
GROUP BY mytable.name

SQL Fiddle