SQL (MariaDB): Remove/ignore from generated results if a single instance is true-CodePudding

I have a table of customer ids and response id:

   customer_id    campaign_id
   20             8
   152            9
   1575           9
   57898          9
   152            10
   1575           10
   888            10

I will be generating another list of customers for campaign 10 again but I need to remove the customer ids that have already responded on campaign 10, easy enough:

Table:

SELECT customer_id FROM responses WHERE campaign_id <> 10

Result:

customer_id
20
152
1575
57898

However my problem is if you notice customer 152 and 1575 have also responded to campaign 9, as such the above query will still include the customers because their response on campaign 9 is not equal to 10.

The query needs to generate a list of customer ids but remove/ignore the customer ids that are equal to campaign 10 completely. If I'm generating on campaign 10 and they've responded on campaign 10 it doesn't matter if they've responded on campaign 8, 9, 200 etc they are not to be included.

My desired results:

customer_id
20
57898

I had attempted to generate customers where responses table is NULL FROM responses WHERE customer_id IS NULL however problems arose from this as because this is campaign 10, customer_id 20 and/or 57898 could technically be allowed to be included as they have no responded from campaign 10.

I hope this make sense, thank you for any assistance.

CodePudding user response：

You can join the same table with exclusion condition and group by cutomer_id, like

SELECT res.customer_id FROM responses res
LEFT JOIN (
  SELECT customer_id FROM responses WHERE campaign_id = 10
) sub ON sub.customer_id = res.customer_id
WHERE sub.customer_id is null
GROUP BY res.customer_id

SQL Fiddle example

In this case, if your join find something then subquery customer_id will be not null