How to apply exclusions criteria when 2 tables are linked by one to many relationship?

Transact-SQL
1

I have a contacts table like this:

Contacts

| CustomerID | CustomerName                       | Country |
|------------|------------------------------------|---------|
| 1          | Alfreds Futterkiste                | Germany |
| 2          | Ana Trujillo Emparedados y helados | Mexico  |
| 3          | Antonio Moreno Taquería            | Mexico  |
| 4          | Around the Horn                    | UK      |
| 5          | Berglunds snabbköp                 | Sweden  |
| 6          | Blauer See Delikatessen            | Germany |
| 7          | Blondel père et fils               | France  |
| 8          | Bólido Comidas preparadas          | Spain   |

I have another table where all customer's exclusions are saved
Foreignkey is CustomerID
Relationship - one to many ...in exclusion table, there can be multiple rows for each customer (1 entry /per exclusion)

CustomerExclusion

| CustomerID | exclusion_type | Flag |
|------------|----------------|------|
| 1          | BadAddress     | Y    |
| 1          | Optout         | Y    |
| 3          | NoCall         | Y    |
| 4          | BadEmail       | Y    |
| 5          | BadAddress     | Y    |

Desired output:
Select all customers from customer table where county is 'Germany' but exclude anyone with 'BadAddress" and exclude anyone with 'Optout'

2

It is best to post consumable test data:

SET ANSI_NULLS, QUOTED_IDENTIFIER, ANSI_PADDING ON;
GO
CREATE TABLE #c
(
  CustomerID int NOT NULL PRIMARY KEY
  ,CustomerName varchar(40) NOT NULL
  ,Country varchar(20) NOT NULL
);
GO
INSERT INTO #C
VALUES (1, 'Alfreds Futterkiste', 'Germany')
	,(2, 'Ana Trujillo Emparedados y helados', 'Mexico')
	,(3, 'Antonio Moreno Taquería', 'Mexico')
	,(4, 'Around the Horn', 'UK')
	,(5, 'Berglunds snabbköp', 'Sweden')
	,(6, 'Blauer See Delikatessen', 'Germany')
	,(7, 'Blondel père et fils', 'France')
	,(8, 'Bólido Comidas preparadas', 'Spain');
GO
CREATE TABLE #X
(
  CustomerID int NOT NULL
  ,exclusion_type varchar(10) NOT NULL
  ,Flag char(1) NOT NULL
  ,PRIMARY KEY (CustomerID, exclusion_type)
);
GO
INSERT INTO #x
VALUES (1, 'BadAddress', 'Y')
  ,(1, 'Optout', 'Y')
  ,(3, 'NoCall', 'Y')
  ,(4, 'BadEmail', 'Y')
  ,(5, 'BadAddress', 'Y');
SELECT C.CustomerID, C.CustomerName, C.Country
FROM #c C
WHERE C.Country = 'Germany' 
	AND NOT EXISTS
	(
		SELECT 1
		FROM #x X
		WHERE X.CustomerID = C.CustomerID
			AND X.exclusion_type IN ('BadAddress','Optout')
	);
3

Hi @Ifor

Thank you so much for your response.

I'll definitely try what you shared.

Just curious for my knowledge, is there any way to do the same via any JOIN? Assuming join performance will be better than NOT EXISTS?

I also thought about doing this with NOT IN approach but it's is not good performance wise.

I have around 20million records in the tables so performance is important.

4

One more question @Ifor

In the existing logic that you suggested, we have

X.exclusion_type IN ('BadAddress','Optout')

since the goal is to select a customer who got both (NOT either one), isn't IN going to pick up customer s who even got one of these two?

Also - since there is Flag value as well. How I can make sure the exclusion type that we are picking also got Flag = Y?

Because a customer can have exclusion_type = BadAddress and Flag = N
and considering each exclusion type got it's own Flag, how to make sure we have Y for all exclusions when we are selecting multiple?

Hope that makes sense. If not, please let me know and I can explain in other words with an example. Thanks again!

5

Exists will probably be more efficient if no rows are required from the second table.

SELECT C.CustomerID, C.CustomerName, C.Country
FROM #c C
WHERE C.Country = 'Germany' 
	AND NOT EXISTS
	(
		SELECT 1
		FROM #x X
		WHERE X.CustomerID = C.CustomerID
			AND X.exclusion_type IN ('BadAddress','Optout')
            AND X.Flag = 'Y'
		GROUP BY X.CustomerID
		HAVING COUNT(*) = 2
	);
6

Hi @Ifor

Thank you for the update - Just curious,

If I am understanding correctly Select 1 along with Group by and having count 2 will pickup only 1 record / per row for customers with multiple rows of exclusions, right?

For reference. a customer with a specific exclusion type can have only 1 row (either with Y or N)

For example - A customer can have 10 different exclusion types ( Y or N /per exclusion type).

Just making sure if that is going to be honored with this logic?

Also - with the use of IN ('BadAddress','Optout') - aren't we telling the sql to pick customers who got one of these two? but in my use case I would like both along with their Y flag.

7
8

Hi @Ifor Thanks for sharing the resource.

So, in this case, GroupBy is simple to bundle up multiple customers rows /per exclusion type? so IN criteria will not work as (anyone out of the list) but will work as AND?

Could you please help me understand?