SQL - child must contain all specified values

问题:

I have two tables with following data:

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

I want to select all rows from table test_parent where parent contains children with (BOTH) properties A and B (so this would be record with parent_id=2)

This is the best solution I wrote so far:

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

Is there any more "correct" way?

Many thanks!

This is full script for objects:

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;

回答1:

I'm not sure about "more correct", but a simple JOIN with GROUP BY/HAVING will do it without a subquery;

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

An SQLfiddle to test with.

It will basically join the parent with any child that has a property equal to 'A' or 'B', group by the parent row and count the distinct values of property on the child. If it's equal to 2 ('A' and 'B' being the two possible values), return the parent.

回答2:

The query in the question

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

has a little problem: if there are two child, both with 'A' or both with 'B' the parent will show in the resultset, an that's different from the requirement stated. It will also not show the parent with more than two child, even if they have only 'A' and 'B' as property, for example if we add the rows

child_id | parent_id | property
       7 |         5 |        A
       8 |         5 |        B
       9 |         5 |        A

to the data of test_child the parent 5 will not be in the resultset (stated that 5 is in the parent table)

Another way to write the query of Joachim Isaksson is to move the check on the child properties to the HAVING clause

SELECT tp.id, tp.title
FROM   test_parent tp
       INNER JOIN test_child tc ON tp.parent_id = tc.parent_id
GROUP BY tp.id, tp.title
HAVING COUNT(DISTINCT tp.property) = 2
   AND SUM(CASE WHEN tp.property IN ('A', 'B') THEN 0 ELSE 1 END) = 0

回答3:

You could try this. I believe it will perform better, but you shoukd check the excecution plans to check this.

 SELECT distinct tp.title
    FROM test_parent tp
    INNER JOIN test_child ca on tp.parent_id=ca.parent_id and ca.property='A'
    INNER JOIN test_child cb on ca.parent_id=cb.parent_id and cb.property='B'