Q:

SQL Query Joining Multiple tables using and critrea

When inner joining two tables the results are essentially "or" driven.  So for example if I had a parent and child table, and I wanted to know that children who have red or blond hair I would write something like: 
SELECT parent.parent_name
FROM parent
INNER JOIN child
ON parent.parent_ID = child.parent_ID
WHERE child.hair = blond OR child.hair = red

This would tell me all parents who have children with red OR blond hair.  What would I write if I wanted to know parents who have at least one child with red hair AND at least one child with blond hair?  Keep in mind that the criteria may change over time - tomorrow I might want to know black and red and yellow and blond and green hair, so writing a query for red and a query for blond and joining the results wont work because sometimes it will be two ANDs, but sometimes more. 
I hope that makes sense.  

A:

This is a great example of when to use a having clause.
You know you want at least one of each color so a distinct will be necessary.
The where clause limited to blond or red already. We grouped by parent so the having clause is only looking at each parents, not all kids; and if the distinct count is 2, then it must be because they have a child with blond hair and one with red hair.
Since you know the criteria each time (red, blond or red, green, yellow blond), the count can vary as well via parameters!
SELECT parent.parent_name
FROM parent
INNER JOIN child
ON parent.parent_ID = child.parent_ID
WHERE child.hair = blond OR child.hair = red
group by parent.parent_name 
Having count(Distinct child.hair) = 2

so as your where clause changes so does the #2.

