CODEWITHSUNDEEP
mysql
indextwo
indextwo

Reputation: 5935

MySQL query to select only entries where value = X and value <> Y

Raw MySQL queries are absolutely not my forte, so I'm struggling with this a bit, but: with a straightforward table layout like this:

+----+-----------+----------+---------------------+
| id | status_id | order_id | created_at          |
+----+-----------+----------+---------------------+
|  1 |     1     |     1    | 2016-03-21 20:40:39 |
|  2 |     3     |     1    | 2016-03-21 20:40:45 |
|  3 |     5     |     1    | 2016-03-21 20:47:14 |
|  4 |     1     |     2    | 2016-03-25 12:14:44 |
|  6 |     3     |     2    | 2016-03-25 12:16:12 |
|  7 |     5     |     2    | 2016-03-25 12:47:43 |
|  8 |     1     |     3    | 2016-03-26 17:25:12 |
|  9 |     3     |     3    | 2016-03-26 17:25:48 |
+----+-----------+----------+---------------------+

I want to select only the order_id rows where the status_id equals 3, but not where that same order_id has a status_id of 5. As a result, my query should only return order ID 3, but my current query returns all 3 order IDs in the results:

$statusQueryString = 'SELECT DISTINCT order_id 
    FROM shop_order_status_log_records 
    WHERE status_id = 3 AND status_id <> 5 ORDER BY created_at';

Where am I going wrong with my query?

Upvotes: 0

Views: 657

Answers (1)

Mihai
Mihai

Reputation: 26784

Use post aggregate filtering when you need 2 or more conditions per group.A simple rule WHERE filters rows HAVING filters groups

SELECT order_id  FROM shop_order_status_log_records 
GROUP BY order_id 
HAVING SUM(status_id = 3)>0
AND SUM(status_id = 5)=0

Upvotes: 3

Related Questions