0

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?

  • I sense ... min(status_id) > 4 might help. – mustaccio Feb 15 '17 at 13:26
  • Where exactly would you put that statement in my query? – Leon Willens Feb 15 '17 at 13:30
  • 1
    Where aggregation is done would be a good spot I presume. – mustaccio Feb 15 '17 at 13:32
  • 2
    so, something along the lines of having count(*) > 1 and min(status_id) > 4, I guess? – Leon Willens Feb 15 '17 at 14:14
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);

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.