I want to delete records in Table A based on whether there are records in Table B.
My database tracks Families (Families table). A family can have one or more family members (People table) in it. Groups of people do things together (Groups table). Each person in a family can participate in one or more groups (GroupsPeople table). A family, people and group belong to an entity (Entities table). Primary and foreign keys are like this:
TABLE PRIMARY AND FOREIGN KEYS
Families FamilyID (PK)
People PeopleID (PK), FamilyID (FK)
Groups GroupID (PK)
GroupsPeople GroupID (FK), PeopleID (FK), EID (FK) -- All three together are a composite PK
Entities EID (PK)
I want to delete from the GroupsPeople table all records for people in a family. Here is the SQL:
DECLARE @EID int
DECLARE @FamilyID int
DECLARE @GroupID int
SET @EID = 1
SET @FamilyID = 8
SET @GroupID = 5
DELETE FROM GroupsPeople
WHERE EID = @EID AND GroupID = @GroupID AND PeopleID IN
( SELECT DISTINCT PeopleID
WHERE EID = @EID AND FamilyID = @FamilyID )
When I run this query I get the following error:
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.
How do I reconstruct this query to make it work?