CODEWITHSUNDEEP
mysql
jacobcan118
jacobcan118

Reputation: 9037

MySQL select rows that do only have some value but not other value

I have a MySQL table with the following pattern:

| customer_id | store_id | id
|-------------|----------|---
| 123         | A        | 1
| 123         | B        | 2
| 123         | C        | 3
| 456         | A        | 4
| 789         | A        | 5
| 789         | C        | 6
| 456         | A        | 7

Say, I want to retrieve all rows that customer_id only have store_id with A but not B

so return result I should have something like

| customer_id | store_id | id
|-------------|----------|----
| 456         | A        | 4
| 456         | A        | 7
| 789         | A        | 5
| 789         | C        | 6

i do try to use not exist but it does not work select * from tt where store_id = 'A' and not exists (select * from tt where store_id != 'B');

Upvotes: 0

Views: 46

Answers (2)

forpas
forpas

Reputation: 164089

If you group by customer_id and set the condition in the having clause you can get all the customer_ids that you want:

select * from tt
where customer_id in (
  select customer_id from tt
  where store_id in ('A', 'B')
  group by customer_id
  having sum(store_id = 'B') = 0
)

See the demo.
Results:

| customer_id | store_id | id  |
| ----------- | -------- | --- |
| 456         | A        | 4   |
| 789         | A        | 5   |
| 789         | C        | 6   |
| 456         | A        | 7   |

Upvotes: 1

Lukasz Szozda
Lukasz Szozda

Reputation: 175706

You could use correlated subquery:

SELECT *
FROM tt
WHERE customer_id IN (
  select customer_id
  from tt t1
  where store_id = 'A' 
  and not exists (select * from tt t2 where t1.customer_id=t2.customer_id and store_id='B'));

Upvotes: 1

Related Questions