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

MySQLselect

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?

Best Answer

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);
Related Question