title: SQL Query to get records of parent table that have a list of child records
body: <p>I have two tables in an MS SQL Server 2005 database, parent and child, where the parent may be related to many child records. [Child.parent_id] is related to [parent.id]. The child table also has column [foo] I need to bring back all records in the parent table where [child.foo] matches on each of one to many parameters. For example I would like all parent records that have a [child.foo] value of 'fizz' and a [child.foo] value of 'buzz.'
  I have tried the below query, but it is returning records that match on only one. </p>
  
  <pre><code>SELECT     Parent.ID
  FROM         Parent 
  INNER JOIN Child ON Parent.ID = Child.parent_id
  WHERE     (Child.foo = 'fizz')
  UNION ALL
  SELECT     Parent_1.ID
  FROM         Parent AS Parent_1 
  INNER JOIN Child AS Child_1 ON Parent_1.ID = Child_1.parent_id
  WHERE     (Child_1.foo = 'buzz')
  </code></pre>