I have a table structure similar to the following. It is a one-to-many relationship, where a parent can handle multiple children.
The query I need to write has three levels of filtering. The first level is to find a parent who has a child named 'Bob' and a child named 'Chris'. Here is the SQL for that query:
FROM Parent p
WHERE ParentID IN (Select ParentID FROM Child WHERE ChildName='Bob')
AND ParentID IN (Select ParentID FROM Child WHERE ChildName='Chris')
Is there any way to eliminate the two subqueries in that statement?
Furthermore, our next level of filtering again has to look at two children and find a child named 'Bob' under the age of 10, and a child named Chris with the gender set to 'Male'. Is there any way to build our first result set to allow filtering that would eliminate the need for a second query? The one-to-many relationship has me stuck.
Thanks so much for the help!