MYSQL: Select rows with identical values in one column, and specific values in another

Question

I have a MySQL table with the following pattern:

| customer_id | store_id | status_id |
|-------------|----------|-----------|
| 12345 | 12 | 5 |
| 12345 | 8 | 3 |
| 12345 | 6 | 8 |
| 14567 | 5 | 3 |
| 14567 | 5 | 6 |
| 16543 | 3 | 4 |
| 19876 | 3 | 5 |
| 19876 | 6 | 8 |

Say, I want to retrieve all rows...

• with identical customer IDs
• which in turn have different store IDs
• which all should have a status ID higher than 4

The correct output for the example table would therefore be:

| customer_id | store_id | status_id |
|-------------|----------|-----------|
| 19876 | 3 | 5 |
| 19876 | 6 | 8 |

Currently, I successfully wrote down a query to select all rows with an identical customer_id:

SELECT *
FROM table
WHERE customer_id IN (
 SELECT customer_id
 FROM table
 GROUP BY customer_id
 HAVING COUNT(*) > 1
)

This query would pick me all rows with customer_id = 12345 as well, but since one of the status_id of these rows is smaller than 4, I don't want this customer_id.

As an additional question, how would the final query look like when ported to Doctrine/Symfony?

• mustaccio: I sense ... min(status_id) > 4 might help.
• Leon Willens: Where exactly would you put that statement in my query?
• mustaccio: Where aggregation is done would be a good spot I presume.
• Leon Willens: so, something along the lines of having count(*) > 1 and min(status_id) > 4, I guess?

Answer 1

SELECT b.*
 FROM (
 SELECT customer_id
 FROM tbl
 WHERE COUNT(*) = COUNT(DISTINCT STORE_id) -- no dup store_ids
 GROUP BY customer_id -- per customer
 HAVING MIN(status_id) > 4 -- all status_id > 4
 ) AS a
 JOIN tbl AS b -- go back to get other columns
 USING(customer_id);