title: SQL - child must contain all specified values
body: <p>I have two tables with following data:</p>
  
  <p><code>Test_parent</code>:</p>
  
  <pre><code>parent_id   title
  ------------------
  1   Parent1
  2   Parent2
  3   Parent3
  4   Parent4
  </code></pre>
  
  <p><code>Test_child</code>:</p>
  
  <pre><code>child_id    parent_id   property
  ------------------------------------
  1   1   A
  2   2   A
  3   2   B
  4   3   A
  5   3   C
  6   4   A
  </code></pre>
  
  <p>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)</p>
  
  <p>This is the best solution I wrote so far:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>Is there any more "correct" way?</p>
  
  <p>Many thanks!</p>
  
  <p>This is full script for objects:</p>
  
  <pre><code>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;
  </code></pre>