SQL - child必须包含所有指定的值

我有两个包含以下数据的表:

Test_parent:

parent_id   title
------------------
1   Parent1
2   Parent2
3   Parent3
4   Parent4

Test_child:

child_id    parent_id   property
------------------------------------
1   1   A
2   2   A
3   2   B
4   3   A
5   3   C
6   4   A

我想从表test_parent中选择所有行,其中parent包含具有(BOTH)属性A和B的子项(所以这将是parent_id = 2的记录)

这是我到目前为止写的最佳解决方案:

select * 
from test_parent p
where (select COUNT(property) 
       from test_child c 
       where p.parent_id = c.parent_id and c.property in ('A', 'B')) = 2

还有更"正确"的方式吗？

非常感谢!

这是对象的完整脚本:

CREATE TABLE [dbo].[test_parent](
    [parent_id] [int] IDENTITY(1,1) NOT NULL,
    [title] [nvarchar](50) NOT NULL,
 CONSTRAINT [PK_test_parent] PRIMARY KEY CLUSTERED 
([parent_id]))
GO

CREATE TABLE [dbo].[test_child](
    [child_id] [int] IDENTITY(1,1) NOT NULL,
    [parent_id] [int] NOT NULL,
    [property] [nvarchar](10) NOT NULL,
 CONSTRAINT [PK_test_child] PRIMARY KEY CLUSTERED 
([child_id]))
GO

ALTER TABLE [dbo].[test_child]  WITH CHECK ADD  CONSTRAINT [FK_test_child_test_child] FOREIGN KEY([parent_id])
REFERENCES [dbo].[test_parent] ([parent_id])
GO

ALTER TABLE [dbo].[test_child] CHECK CONSTRAINT [FK_test_child_test_child]
GO

SET IDENTITY_INSERT [dbo].[test_parent] ON;
INSERT INTO [dbo].[test_parent]([parent_id], [title])
SELECT 1, N'Parent1' UNION ALL
SELECT 2, N'Parent2' UNION ALL
SELECT 3, N'Parent3' UNION ALL
SELECT 4, N'Parent4'

SET IDENTITY_INSERT [dbo].[test_parent] OFF;
GO

SET IDENTITY_INSERT [dbo].[test_child] ON;

INSERT INTO [dbo].[test_child]([child_id], [parent_id], [property])
SELECT 1, 1, N'A' UNION ALL
SELECT 2, 2, N'A' UNION ALL
SELECT 3, 2, N'B' UNION ALL
SELECT 4, 3, N'A' UNION ALL
SELECT 5, 3, N'C' UNION ALL
SELECT 6, 4, N'A'
GO

SET IDENTITY_INSERT [dbo].[test_child] OFF;

Joachim Isak.. 6

我不确定"更正确",但是使用GROUP BY/HAVING的简单JOIN将在没有子查询的情况下完成;

SELECT test_parent.parent_id, test_parent.title 
FROM test_parent
JOIN test_child ON test_child.parent_id=test_parent.parent_id
 AND test_child.property IN ('A','B')
GROUP BY test_parent.parent_id, test_parent.title
HAVING COUNT(DISTINCT test_child.property)=2

一个要测试的SQLfiddle.

它基本上将父母与任何具有等于"A"或"B"属性的子项连接,由父行分组并计算子项上的不同值property.如果它等于2('A'和'B'是两个可能的值),则返回父项.