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.
INNER JOIN Child ON Parent.ID = Child.parent_id
WHERE (Child.foo = 'fizz')
FROM Parent AS Parent_1
INNER JOIN Child AS Child_1 ON Parent_1.ID = Child_1.parent_id
WHERE (Child_1.foo = 'buzz')
This will return all Parent records which have [at least] one child with a 'fizz' foo AND [at least] one child with 'buzz' foo. Which is what I think is required in the question.
Also, while being potentially sub-optimal, this query is generic in a sense that it will work with most SQL implementation, not only the more modern ones, where the support of CTE, subqueries and related constructs are supported.
SELECT DISTINCT Parent.ID FROM Parent JOIN Child C1 ON Parent.ID = C1.parent_Id JOIN Child C2 ON Parent.ID = C2.parent_id WHERE C1.foo = 'fizz' AND C2.foo = 'buzz'
Now that Joel Potter has fixed the query in his answer, we probably agree that his approach has several advantages over the query listed above (please give him a few +reps). In particular:
Following is Joel's query here, slightly modified, to show how it can be expanded for more than 2 foo values.
SELECT Parent.Id FROM Parent INNER JOIN Child on Parent.Id = child.parent_id WHERE Child.foo IN ('fizz', 'buzz') -- or say, ... IN ('fizz', 'buzz', 'bang', 'dong') GROUP BY Parent.Id HAVING COUNT(DISTINCT Child.foo) = 2 -- or 4 ...