How to apply inclusion and exclusion from the same table in SQL criteria?

Transact-SQL
23

Thank you @Ifor for your prompt response.

Just double checking - since goal is to select customers who got Profile Attribute = DOB with flag = Y AND SubscriptionPlanHolder with flag = Y

While L.ProfileAttribute IN ('DOB','SubscriptionPlanHolder') approach is in some ways OR condition (not AND), the group by in this case is checking for true case for BOTH?

24

I think you should set up a small test environment and see what happens.

25

Question - 1- Could you please share why count = 2? why 2? is it because we are checking 2 values? if I have 3 values to check in criteria then count = 3? and so on...?

Question - 2- In the top level select statement where we have
SELECT C.CustomerID, C.CustomerName, C.Country
How can I add some field "ProfileAttribute & Flag" from profile attribute table as well?

I have tried doing like this:

SELECT C.CustomerID, C.CustomerName, C.Country, P.ProfileAttribute, P.Flag
FROM dbo.Contacts C
	JOIN
	(
		SELECT L.CustomerID, L.ProfileAttribute, L.Flag
....
.......

I am getting an error saying

not contained in either an aggregate function or the GROUP BY clause.

26

This is VERY basic standard SQL. If you have any doubts look it up.

This was not in your original question:

I am sure with a little imagination you can work it out.

27
create data script

drop table #Contacts
create table #Contacts
(
CustomerID INT,
CustomerName VARCHAR(100),
Country VARCHAR(100)
)
insert into #Contacts select 1 ,'Alfreds Futterkiste','Germany'
insert into #Contacts select 2 ,'Ana Trujillo Emparedados y helados' , 'Mexico'
insert into #Contacts select 3 ,'Antonio Moreno Taquería' ,'Mexico'
insert into #Contacts select 4 ,'Around the Horn' ,'UK'
insert into #Contacts select 5 ,'Berglunds snabbköp' ,'Sweden'
insert into #Contacts select 6 ,'Blauer See Delikatessen' ,'Germany'
insert into #Contacts select 7 ,'Blondel père et fils' ,'France'
insert into #Contacts select 8 ,'Bólido Comidas preparadas' ,'Spain'
insert into #Contacts select 9 ,'John' ,'Germany'
select * from #Contacts

drop table #CustomerExclusion
create table #CustomerExclusion
( CustomerID int, exclusion_type varchar(100), Flag varchar(1))
insert into #CustomerExclusion select 1 ,'BadAddress','Y'
insert into #CustomerExclusion select 1 ,'Optout','Y'
insert into #CustomerExclusion select 3 ,'NoCall','Y'
insert into #CustomerExclusion select 4 ,'BadEmail','Y'
insert into #CustomerExclusion select 5 ,'BadAddress','Y'
insert into #CustomerExclusion select 6 ,'BadAddress ','Y'
insert into #CustomerExclusion select 6 ,'Optout','Y'
insert into #CustomerExclusion select 9 ,'BadAddress','N'
select * from #CustomerExclusion

drop table #CustomerProfile
create table #CustomerProfile
( CustomerID INT,profile_attribute varchar(100), Flag varchar(1) ,Effective_Date date)
insert into #CustomerProfile select 1 ,'DOB ','Y ','11/19/2022'
insert into #CustomerProfile select 1 ,'DoNotContact ','Y ','10/25/2022'
insert into #CustomerProfile select 3 ,'AddressOnFile ','Y ','9/13/2022 '
insert into #CustomerProfile select 4 ,'SubscriptionPlanHolder ','Y ','8/8/2022 '
insert into #CustomerProfile select 5 ,'DOB ','N ','11/1/2022 '
insert into #CustomerProfile select 5 ,'DoNotContact ','Y ','10/2/2022 '
insert into #CustomerProfile select 5 ,'SubscriptionPlanHolder ','Y ','5/1/2022 '
insert into #CustomerProfile select 6 ,'DOB ','Y ','10/27/2021'
insert into #CustomerProfile select 6 ,'SubscriptionPlanHolder ','Y ','11/11/2022'
insert into #CustomerProfile select 6 ,'AddressOnFile ','N ','10/1/2022 '
insert into #CustomerProfile select 9 ,'DOB ','Y ','9/9/2022 '
insert into #CustomerProfile select 9 ,'SubscriptionPlanHolder ','N ','8/19/2022 '
select * from #CustomerProfile

VARIETY way of doing

 select CustomerID from #Contacts where Country = 'GERMANY'
 INTERSECT 
 select CustomerID from #CustomerProfile where profile_attribute like '%DOB%' and Flag= 'Y'
 INTERSECT 
 select CustomerID from #CustomerProfile where profile_attribute like '%SubscriptionPlanHolder%' and Flag= 'Y'
 INTERSECT
 select CustomerID from #CustomerExclusion where  exclusion_type = 'BadAddress' and Flag = 'Y'
 INTERSECT
 select CustomerID from #CustomerExclusion where  exclusion_type = 'Optout' and Flag = 'Y'

image