get-records-that-have-all-sub-records

USE [TestDb]
GO
CREATE TABLE [dbo].[Medication_Symptom](
    [Id] [INT] NOT NULL,
    [MedicineId] [INT] NOT NULL,
    [SymptomId] [INT] NOT NULL,
 CONSTRAINT [PK_MedicineSymptom] PRIMARY KEY CLUSTERED
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]

GO
CREATE TABLE [dbo].[Medicine](
    [Id] [INT] NOT NULL,
    [Name] [NCHAR](10) NOT NULL,
 CONSTRAINT [PK_Medicine] PRIMARY KEY CLUSTERED
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]

GO
CREATE TABLE [dbo].[Patient](
    [Id] [INT] NOT NULL,
    [Name] [NCHAR](10) NOT NULL,
 CONSTRAINT [PK_Patient] PRIMARY KEY CLUSTERED
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]

GO
CREATE TABLE [dbo].[Patient_Symptom](
    [Id] [INT] NOT NULL,
    [PatientId] [INT] NOT NULL,
    [SymptomId] [INT] NOT NULL,
 CONSTRAINT [PK_PatientSymptom] PRIMARY KEY CLUSTERED
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]

GO
CREATE TABLE [dbo].[Symptom](
    [Id] [INT] NOT NULL,
    [Name] [NCHAR](10) NOT NULL,
 CONSTRAINT [PK_Symptom] PRIMARY KEY CLUSTERED
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]

GO
INSERT [dbo].[Medication_Symptom] ([Id], [MedicineId], [SymptomId]) VALUES (1, 1, 1)
GO
INSERT [dbo].[Medication_Symptom] ([Id], [MedicineId], [SymptomId]) VALUES (2, 1, 2)
GO
INSERT [dbo].[Medication_Symptom] ([Id], [MedicineId], [SymptomId]) VALUES (3, 2, 3)
GO
INSERT [dbo].[Medication_Symptom] ([Id], [MedicineId], [SymptomId]) VALUES (4, 2, 4)
GO
INSERT [dbo].[Medication_Symptom] ([Id], [MedicineId], [SymptomId]) VALUES (5, 3, 5)
GO
INSERT [dbo].[Medication_Symptom] ([Id], [MedicineId], [SymptomId]) VALUES (6, 4, 1)
GO
INSERT [dbo].[Medication_Symptom] ([Id], [MedicineId], [SymptomId]) VALUES (7, 4, 3)
GO
INSERT [dbo].[Medication_Symptom] ([Id], [MedicineId], [SymptomId]) VALUES (8, 4, 4)
GO
INSERT [dbo].[Medication_Symptom] ([Id], [MedicineId], [SymptomId]) VALUES (9, 5, 6)
GO
INSERT [dbo].[Medication_Symptom] ([Id], [MedicineId], [SymptomId]) VALUES (10, 5, 7)
GO
INSERT [dbo].[Medication_Symptom] ([Id], [MedicineId], [SymptomId]) VALUES (11, 6, 1)
GO
INSERT [dbo].[Medication_Symptom] ([Id], [MedicineId], [SymptomId]) VALUES (12, 6, 3)
GO
INSERT [dbo].[Medication_Symptom] ([Id], [MedicineId], [SymptomId]) VALUES (13, 7, 1)
GO
INSERT [dbo].[Medication_Symptom] ([Id], [MedicineId], [SymptomId]) VALUES (14, 7, 2)
GO
INSERT [dbo].[Medication_Symptom] ([Id], [MedicineId], [SymptomId]) VALUES (15, 7, 3)
GO
INSERT [dbo].[Medication_Symptom] ([Id], [MedicineId], [SymptomId]) VALUES (16, 7, 4)
GO
INSERT [dbo].[Medication_Symptom] ([Id], [MedicineId], [SymptomId]) VALUES (17, 7, 5)
GO
INSERT [dbo].[Medication_Symptom] ([Id], [MedicineId], [SymptomId]) VALUES (18, 7, 6)
GO
INSERT [dbo].[Medication_Symptom] ([Id], [MedicineId], [SymptomId]) VALUES (19, 7, 7)
GO
INSERT [dbo].[Medicine] ([Id], [Name]) VALUES (1, N'MedA      ')
GO
INSERT [dbo].[Medicine] ([Id], [Name]) VALUES (2, N'MedB      ')
GO
INSERT [dbo].[Medicine] ([Id], [Name]) VALUES (3, N'MedC      ')
GO
INSERT [dbo].[Medicine] ([Id], [Name]) VALUES (4, N'MedD      ')
GO
INSERT [dbo].[Medicine] ([Id], [Name]) VALUES (5, N'MedE      ')
GO
INSERT [dbo].[Medicine] ([Id], [Name]) VALUES (6, N'MedF      ')
GO
INSERT [dbo].[Medicine] ([Id], [Name]) VALUES (7, N'MedAll    ')
GO
INSERT [dbo].[Patient] ([Id], [Name]) VALUES (1, N'PA        ')
GO
INSERT [dbo].[Patient] ([Id], [Name]) VALUES (2, N'PB        ')
GO
INSERT [dbo].[Patient] ([Id], [Name]) VALUES (3, N'PC        ')
GO
INSERT [dbo].[Patient] ([Id], [Name]) VALUES (4, N'PD        ')
GO
INSERT [dbo].[Patient] ([Id], [Name]) VALUES (5, N'PE        ')
GO
INSERT [dbo].[Patient] ([Id], [Name]) VALUES (6, N'PF        ')
GO
INSERT [dbo].[Patient] ([Id], [Name]) VALUES (7, N'PAll      ')
GO
INSERT [dbo].[Patient_Symptom] ([Id], [PatientId], [SymptomId]) VALUES (1, 1, 1)
GO
INSERT [dbo].[Patient_Symptom] ([Id], [PatientId], [SymptomId]) VALUES (2, 1, 2)
GO
INSERT [dbo].[Patient_Symptom] ([Id], [PatientId], [SymptomId]) VALUES (3, 2, 3)
GO
INSERT [dbo].[Patient_Symptom] ([Id], [PatientId], [SymptomId]) VALUES (4, 2, 4)
GO
INSERT [dbo].[Patient_Symptom] ([Id], [PatientId], [SymptomId]) VALUES (5, 3, 5)
GO
INSERT [dbo].[Patient_Symptom] ([Id], [PatientId], [SymptomId]) VALUES (6, 5, 1)
GO
INSERT [dbo].[Patient_Symptom] ([Id], [PatientId], [SymptomId]) VALUES (7, 5, 3)
GO
INSERT [dbo].[Patient_Symptom] ([Id], [PatientId], [SymptomId]) VALUES (8, 6, 7)
GO
INSERT [dbo].[Patient_Symptom] ([Id], [PatientId], [SymptomId]) VALUES (9, 7, 1)
GO
INSERT [dbo].[Patient_Symptom] ([Id], [PatientId], [SymptomId]) VALUES (10, 7, 2)
GO
INSERT [dbo].[Patient_Symptom] ([Id], [PatientId], [SymptomId]) VALUES (11, 7, 3)
GO
INSERT [dbo].[Patient_Symptom] ([Id], [PatientId], [SymptomId]) VALUES (12, 7, 4)
GO
INSERT [dbo].[Patient_Symptom] ([Id], [PatientId], [SymptomId]) VALUES (13, 7, 5)
GO
INSERT [dbo].[Patient_Symptom] ([Id], [PatientId], [SymptomId]) VALUES (14, 7, 6)
GO
INSERT [dbo].[Patient_Symptom] ([Id], [PatientId], [SymptomId]) VALUES (15, 7, 7)
GO
INSERT [dbo].[Symptom] ([Id], [Name]) VALUES (1, N'SA        ')
GO
INSERT [dbo].[Symptom] ([Id], [Name]) VALUES (2, N'SB        ')
GO
INSERT [dbo].[Symptom] ([Id], [Name]) VALUES (3, N'SC        ')
GO
INSERT [dbo].[Symptom] ([Id], [Name]) VALUES (4, N'SD        ')
GO
INSERT [dbo].[Symptom] ([Id], [Name]) VALUES (5, N'SE        ')
GO
INSERT [dbo].[Symptom] ([Id], [Name]) VALUES (6, N'SF        ')
GO
INSERT [dbo].[Symptom] ([Id], [Name]) VALUES (7, N'SG        ')
GO
ALTER TABLE [dbo].[Medication_Symptom]  WITH CHECK ADD  CONSTRAINT [FK_MedicineSymptom_Medicine] FOREIGN KEY([MedicineId])
REFERENCES [dbo].[Medicine] ([Id])
GO
ALTER TABLE [dbo].[Medication_Symptom] CHECK CONSTRAINT [FK_MedicineSymptom_Medicine]
GO
ALTER TABLE [dbo].[Medication_Symptom]  WITH CHECK ADD  CONSTRAINT [FK_MedicineSymptom_Symptom] FOREIGN KEY([SymptomId])
REFERENCES [dbo].[Symptom] ([Id])
GO
ALTER TABLE [dbo].[Medication_Symptom] CHECK CONSTRAINT [FK_MedicineSymptom_Symptom]
GO
ALTER TABLE [dbo].[Patient_Symptom]  WITH CHECK ADD  CONSTRAINT [FK_PatientSymptom_Patient] FOREIGN KEY([PatientId])
REFERENCES [dbo].[Patient] ([Id])
GO
ALTER TABLE [dbo].[Patient_Symptom] CHECK CONSTRAINT [FK_PatientSymptom_Patient]
GO
ALTER TABLE [dbo].[Patient_Symptom]  WITH CHECK ADD  CONSTRAINT [FK_PatientSymptom_Symptom] FOREIGN KEY([SymptomId])
REFERENCES [dbo].[Symptom] ([Id])
GO
ALTER TABLE [dbo].[Patient_Symptom] CHECK CONSTRAINT [FK_PatientSymptom_Symptom]
GO