MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE related
(
    Person1_UID int,
	Person2_UID int,
    Type varchar(100)
);

INSERT INTO related VALUES(1,2,'club member'),(1,9, 'family')
,(1,11,'colleague')
,(2,4, 'friend')
,(2,7, 'colleague')
,(3,4, 'club member')
,(3,6, 'colleague')
,(3,9, 'friend')
,(3,10,'follower')
,(4,6, 'family')
,(4,10,'family')
,(5,8, 'family')
,(6,11,'colleague')
,(7,8, 'colleague')
,(8,11,'friend');
	
SELECT * FROM related;

Records: 15  Duplicates: 0  Warnings: 0




Person1_UID
Person2_UID
Type


1
2
club member

1
9
family

1
11
colleague

2
4
friend

2
7
colleague

3
4
club member

3
6
colleague

3
9
friend

3
10
follower

4
6
family

4
10
family

5
8
family

6
11
colleague

7
8
colleague

8
11
friend


CREATE TABLE day_package
(
    DID int,
    Description varchar(100),
	UID int,
	VID int
);

INSERT INTO day_package VALUES
(1,'Spa Day',1,19),
(1,'Spa Day',2,20),
(1,'Spa Day',9,17),
(2,'Adventure Time',4,16),
(2,'Adventure Time',6,16),
(3,'Family Fun Day',3,18),
(3,'Family Fun Day',4,17),
(3,'Family Fun Day',7,18),
(3,'Family Fun Day',10,19),
(4,'Relaxation Date',5,20);

SELECT * FROM day_package;

Records: 10  Duplicates: 0  Warnings: 0




DID
Description
UID
VID


1
Spa Day
1
19

1
Spa Day
2
20

1
Spa Day
9
17

2
Adventure Time
4
16

2
Adventure Time
6
16

3
Family Fun Day
3
18

3
Family Fun Day
4
17

3
Family Fun Day
7
18

3
Family Fun Day
10
19

4
Relaxation Date
5
20


WITH CTE AS
(
  SELECT dp1.DID,
  dp1.Description,
  dp1.uid as uid1,
  dp2.uid as uid2,
  COUNT(*) OVER(PARTITION BY dp1.DID,dp1.UID) as total_participants,
  COUNT(r.type in ('club member','family') or dp1.UID = dp2.UID) 
  OVER(PARTITION BY dp1.DID,dp1.UID) as related_participants
  FROM day_package dp1
  LEFT JOIN day_package dp2
  ON dp1.did = dp2.did
  LEFT JOIN related r
  ON dp1.UID = r.person1_uid AND dp2.UID = r.person2_UID
)
SELECT * FROM CTE
ORDER BY DID,UID1,UID2


DID
Description
uid1
uid2
total_participants
related_participants


1
Spa Day
1
1
3
3

1
Spa Day
1
2
3
3

1
Spa Day
1
9
3
3

1
Spa Day
2
1
3
1

1
Spa Day
2
2
3
1

1
Spa Day
2
9
3
1

1
Spa Day
9
1
3
1

1
Spa Day
9
2
3
1

1
Spa Day
9
9
3
1

2
Adventure Time
4
4
2
2

2
Adventure Time
4
6
2
2

2
Adventure Time
6
4
2
1

2
Adventure Time
6
6
2
1

3
Family Fun Day
3
3
4
3

3
Family Fun Day
3
4
4
3

3
Family Fun Day
3
7
4
3

3
Family Fun Day
3
10
4
3

3
Family Fun Day
4
3
4
2

3
Family Fun Day
4
4
4
2

3
Family Fun Day
4
7
4
2

3
Family Fun Day
4
10
4
2

3
Family Fun Day
7
3
4
1

3
Family Fun Day
7
4
4
1

3
Family Fun Day
7
7
4
1

3
Family Fun Day
7
10
4
1

3
Family Fun Day
10
3
4
1

3
Family Fun Day
10
4
4
1

3
Family Fun Day
10
7
4
1

3
Family Fun Day
10
10
4
1

4
Relaxation Date
5
5
1
1


WITH CTE AS
(
  SELECT dp1.DID,
  dp1.Description,
  dp1.uid as uid1,
  dp2.uid as uid2,
  COUNT(*) OVER(PARTITION BY dp1.DID,dp1.UID) as total_participants,
  COUNT(r.type in ('club member','family') or dp1.UID = dp2.UID) 
  OVER(PARTITION BY dp1.DID,dp1.UID) as related_participants
  FROM day_package dp1
  LEFT JOIN day_package dp2
  ON dp1.did = dp2.did
  LEFT JOIN related r
  ON dp1.UID = r.person1_uid AND dp2.UID = r.person2_UID
)
SELECT DISTINCT DID,
  Description,
  related_participants as Participant_Count
FROM CTE
WHERE related_participants = total_participants
AND total_participants != 1
ORDER BY DID,Participant_Count


DID
Description
Participant_Count


1
Spa Day
3

2
Adventure Time
2

Person1_UID	Person2_UID	Type
1	2	club member
1	9	family
1	11	colleague
2	4	friend
2	7	colleague
3	4	club member
3	6	colleague
3	9	friend
3	10	follower
4	6	family
4	10	family
5	8	family
6	11	colleague
7	8	colleague
8	11	friend

DID	Description	UID	VID
1	Spa Day	1	19
1	Spa Day	2	20
1	Spa Day	9	17
2	Adventure Time	4	16
2	Adventure Time	6	16
3	Family Fun Day	3	18
3	Family Fun Day	4	17
3	Family Fun Day	7	18
3	Family Fun Day	10	19
4	Relaxation Date	5	20