How to select records when table includes multiple rows for each record with multi-criteria

asp__developer · December 21, 2022, 12:37am

I have a Student table:

| **Student**   |                                    |
|-----------|------------------------------------|
| StudentID | StudentName                        |
| 1         | Alfreds Futterkiste                |
| 2         | Ana Trujillo Emparedados y helados |
| 3         | Antonio Moreno Taquería            |
| 4         | Around the Horn                    |
| 5         | Berglunds snabbköp                 |
| 6         | Blauer See Delikatessen            |
| 7         | Blondel père et fils               |
| 8         | Bólido Comidas preparadas          |
| 9         | John                               |

I have a Student Profile table

| **StudentProfile** |                        |      |            |
|----------------|------------------------|------|------------|
| StudentID      | Profile_Category       | Flag |            |
| 1              | DOB                    | Y    | 11/19/2022 |
| 1              | DoNotContact           | Y    | 10/25/2022 |
| 3              | AddressOnFile          | Y    | 9/13/2022  |
| 4              | SubscriptionPlanHolder | Y    | 8/8/2022   |
| 5              | DOB                    | N    | 11/1/2022  |
| 5              | DoNotContact           | Y    | 10/2/2022  |
| 5              | SubscriptionPlanHolder | Y    | 5/1/2022   |
| 6              | DOB                    | Y    | 10/27/2021 |
| 6              | SubscriptionPlanHolder | Y    | 11/11/2022 |
| 6              | AddressOnFile          | N    | 10/1/2022  |
| 9              | DOB                    | Y    | 9/9/2022   |
| 9              | SubscriptionPlanHolder | N    | 8/19/2022  |
| 10             | DOB                    | Y    | 11/11/2022 |
| 10             | SubscriptionPlanHolder | Y    | 10/1/2022  |
| 10             | AddressOnFile          | Y    | 9/9/2022   |
| 10             | DoNotContact           | Y    | 8/19/2022  |
| 11             | DOB                    | Y    | 11/11/2022 |
| 11             | SubscriptionPlanHolder | Y    | 10/1/2022  |
| 11             | AddressOnFile          | Y    | 9/9/2022   |
| 11             | DoNotContact           | Y    | 8/19/2022  |

Desired output items

Select all students who got following profile attributes
(Profile_Category = DOB and Flag = 'Y') AND (Profile_Category = SubscriptionPlanHolder and Flag = 'Y')

*In this case I should get StudentID 6, 10, 11

Select all students who got following profile attributes
(Profile_Category = DOB and Flag = 'Y') AND (Profile_Category = SubscriptionPlanHolder and Flag = 'Y') AND (Profile_Category = AddressOnFile and Flag = 'Y')

*In this case I should get StudentID 10, 11

Select all students who got following profile attributes
(Profile_Category = DOB and Flag = 'Y') AND (Profile_Category = SubscriptionPlanHolder and Flag = 'Y') AND (Profile_Category = AddressOnFile and Flag = 'Y') AND (Profile_Category = DoNotContact and Flag = 'Y')

*In this case I should get StudentID 10, 11

asp__developer · December 21, 2022, 12:39am

So far, I have tried this approach but NOT getting the desired output:

For desired output #1

Select c.StudentID
FROM Student c
INNER JOIN StudentProfile p
ON p.StudentID = c.StudentID
WHERE Profile_Category IN ('DOB','SubscriptionPlanHolder')
AND Flag = 'Y'
GROUP BY c.StudentID
	HAVING COUNT(*) = 2

Getting: 6

For desired output #2

Select c.StudentID
FROM Student c
INNER JOIN StudentProfile p
ON p.StudentID = c.StudentID
WHERE Profile_Category IN ('DOB','SubscriptionPlanHolder','AddressOnFile')
AND Flag = 'Y'
GROUP BY c.StudentID
	HAVING COUNT(*) = 3

Getting: 0 results

For desired output #3

Select c.StudentID
FROM Student c
INNER JOIN StudentProfile p
ON p.StudentID = c.StudentID
WHERE Profile_Category IN ('DOB','SubscriptionPlanHolder', 'DoNotContact', 'AddressOnFile')
AND Flag = 'Y'
GROUP BY c.StudentID
	HAVING COUNT(*) = 4

Getting: 0 results

Where I am getting confused is, how to bundle the different Profile_Category together because I am NOT looking for any one Profile_Category (not looking for OR), goal is to get students who for all the desired Profile_Category (looking for AND) and corresponding flag is Y

robert_volk · December 21, 2022, 2:27am

SELECT StudentID FROM StudentProfile WHERE Flag = 'Y' AND Profile_Category='DOB'
INTERSECT 
SELECT StudentID FROM StudentProfile WHERE Flag = 'Y' AND Profile_Category='SubscriptionPlanHolder'
INTERSECT 
SELECT StudentID FROM StudentProfile WHERE Flag = 'Y' AND Profile_Category='DoNotContact'
INTERSECT 
SELECT StudentID FROM StudentProfile WHERE Flag = 'Y' AND Profile_Category='AddressOnFile'

FYI your DDL and subsequent example query had different column names (Profile_Attribute vs. Profile_Category). I tested the above successfully against the StudentProfile data you posted first.

harishgg1 · December 21, 2022, 6:15am

create data script

drop table #Student
create table #Student (StudentID int , StudentName varchar(100))

insert into #Student select 1 ,'Alfreds Futterkiste'
insert into #Student select 2 ,'Ana Trujillo Emparedados y helados'
insert into #Student select 3 ,'Antonio Moreno Taquería'
insert into #Student select 4 ,'Around the Horn'
insert into #Student select 5 ,'Berglunds snabbköp'
insert into #Student select 6 ,'Blauer See Delikatessen'
insert into #Student select 7 ,'Blondel père et fils'
insert into #Student select 8 ,'Bólido Comidas preparadas'
insert into #Student select 9 ,'John'

select * from #Student

drop table #Student_Profile
create table #Student_Profile(StudentID int, Profile_Category varchar(100), Flag varchar(1) , DOB date )

insert into #Student_Profile select 1 ,'DOB','Y','11/19/2022'
insert into #Student_Profile select 1 ,'DoNotContact','Y','10/25/2022'
insert into #Student_Profile select 3 ,'AddressOnFile','Y','9/13/2022'
insert into #Student_Profile select 4 ,'SubscriptionPlanHolder','Y','8/8/2022'
insert into #Student_Profile select 5 ,'DOB','N','11/1/2022'
insert into #Student_Profile select 5 ,'DoNotContact','Y','10/2/2022'
insert into #Student_Profile select 5 ,'SubscriptionPlanHolder','Y','5/1/2022'
insert into #Student_Profile select 6 ,'DOB','Y','10/27/2021'
insert into #Student_Profile select 6 ,'SubscriptionPlanHolder','Y','11/11/2022'
insert into #Student_Profile select 6 ,'AddressOnFile','N','10/1/2022'
insert into #Student_Profile select 9 ,'DOB','Y','9/9/2022'
insert into #Student_Profile select 9 ,'SubscriptionPlanHolder','N','8/19/2022'
insert into #Student_Profile select 10 ,'DOB','Y','11/11/2022'
insert into #Student_Profile select 10 ,'SubscriptionPlanHolder','Y','10/1/2022'
insert into #Student_Profile select 10 ,'AddressOnFile','Y','9/9/2022'
insert into #Student_Profile select 10 ,'DoNotContact','Y','8/19/2022'
insert into #Student_Profile select 11 ,'DOB','Y','11/11/2022'
insert into #Student_Profile select 11 ,'SubscriptionPlanHolder','Y','10/1/2022'
insert into #Student_Profile select 11 ,'AddressOnFile','Y','9/9/2022'
insert into #Student_Profile select 11 ,'DoNotContact','Y','8/19/2022'

-- For desired output #1
SELECT 
    StudentID
  , count(*) 
FROM 
     #Student_Profile 
WHERE 
     Flag = 'Y' and Profile_Category in  ('DOB', 'SubscriptionPlanHolder')
GROUP BY
     StudentID
HAVING  
     count(*) = 2

-- For desired output #2
SELECT 
     StudentID
  , count(*)
FROM 
     #Student_Profile 
WHERE 
      Profile_Category IN ('DOB','SubscriptionPlanHolder','AddressOnFile')
AND 
     Flag = 'Y'
GROUP BY 
     c.StudentID
HAVING 
     count(*) = 3

-- For desired output #3
SELECT 
    StudentID
  , count(*)
FROM 
     #Student_Profile 
WHERE 
      Profile_Category IN ('DOB','SubscriptionPlanHolder','AddressOnFile','DoNotContact')
AND 
     Flag = 'Y'
GROUP BY 
     c.StudentID
HAVING 
    count(*) = 4

asp__developer · December 21, 2022, 9:47am

Hi @robert_volk

Thanks for the call out. I have corrected Profile_Category name (it was a typo)

Is there any alternative method since I tried INTERSECT but it doesn't seem to be working in the SQL environment I'm working on.

Ifor · December 21, 2022, 9:56am

You have not said what is wrong with the output!!!!!!!

I suspect the most likely problem is duplicate profile categories in which case change the HAVING from COUNT(*) to COUNT(DISTINCT Profile_Category).

As you continually waste people's time by not posting DDL and test data, this is just another guess.

asp__developer · December 21, 2022, 1:10pm

Hi @Ifor my apologies, I will try to post DDL it in future

I have updated my 2nd response above (queries + output) that I am getting.

I would appreciate your help.

asp__developer · December 21, 2022, 1:22pm

Hi @harishgg1

For some reason I am getting the following output:

Select c.StudentID
FROM Student c
INNER JOIN StudentProfile p
ON p.StudentID = c.StudentID
WHERE Profile_Category IN ('DOB','SubscriptionPlanHolder')
AND Flag = 'Y'
GROUP BY c.StudentID
	HAVING COUNT(*) = 2

Getting: 6

Select c.StudentID
FROM Student c
INNER JOIN StudentProfile p
ON p.StudentID = c.StudentID
WHERE Profile_Category IN ('DOB','SubscriptionPlanHolder','AddressOnFile')
AND Flag = 'Y'
GROUP BY c.StudentID
	HAVING COUNT(*) = 3

Getting: 0 results

Select c.StudentID
FROM Student c
INNER JOIN StudentProfile p
ON p.StudentID = c.StudentID
WHERE Profile_Category IN ('DOB','SubscriptionPlanHolder', 'DoNotContact', 'AddressOnFile')
AND Flag = 'Y'
GROUP BY c.StudentID
	HAVING COUNT(*) = 4

Getting: 0 results

harishgg1 · December 21, 2022, 4:01pm

hi

did you not see my query

please see the COUNT(*) i put

asp__developer · December 21, 2022, 10:14pm

I did and I do not need count() in select statement in my use-case. It seems to be working for me without count() column in select statement.

I had issue with my Students table where I missed inserting the record for ID 10 & 11 hence why I was not getting the desired result.

robert_volk · December 22, 2022, 4:34am

I haven't tested this yet but I believe it should work:

SELECT StudentID FROM StudentProfile WHERE Flag = 'Y' AND Profile_Category='DOB'
AND StudentID IN( 
SELECT StudentID FROM StudentProfile WHERE Flag = 'Y' AND Profile_Category='SubscriptionPlanHolder')
AND StudentID IN( 
SELECT StudentID FROM StudentProfile WHERE Flag = 'Y' AND Profile_Category='DoNotContact')
AND StudentID IN( 
SELECT StudentID FROM StudentProfile WHERE Flag = 'Y' AND Profile_Category='AddressOnFile')

That's not identical to INTERSECT as it will not remove duplicates, simply change the topmost SELECT to SELECT DISTINCT if you need it.

Are you using Microsoft SQL Server or another database? INTERSECT is a standard SQL keyword, and SQLTeam.com is a MS SQL Server site. It would help to understand why exactly INTERSECT didn't work, was there an error message?