Optimizing SQL query

I have a table structure similar to the following.  It is a one-to-many relationship, where a parent can handle multiple children.

Parent table
ParentId
ParentName
ParentIncome
ParentGender

Child table
ParentId
ChildId
ChildName
ChildAge
ChildGender

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:

SELECT p.ParentName
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!
Pete
CL0V3RAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
no, you need to have that in 2 conditions to make sure the parent has indeed both children.

for second part:

SELECT p.ParentName
FROM Parent p
WHERE ParentID IN (Select ParentID FROM Child WHERE ChildName='Bob' AND age <= 10)
AND ParentID IN (Select ParentID FROM Child WHERE ChildName='Chris' and gender = 'male')

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
APoPhySptCommented:
Sorry angellll... If I understood it correctly, this way is a lot better:

 
Select
 p.ParentName
From
 Parent p
  Inner Join Child c
   On
    c.ParentID = p.ParentID
     And (
          (c.ChildName='Bob' AND c.age <= 10) 
           or (c.ChildName='Chris' and c,gender = 'male')
          )

Open in new window

0
CL0V3RAuthor Commented:
Hi APoPhySpt,

I think the issue with that approach is the 'or' statement.  I need to match the parent to both records, not either.  Am I missing something?

Thanks,
Pete
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

APoPhySptCommented:
Both Bob's under 10 year old and Chris's that are men will be selected from the Child table. Therefore, only their parents will be selected.

This way, only one select statement is used to do the whole query!
0
penyCuicasCommented:
you can try this

SELECT p.ParentName
FROM Parent p
WHERE EXISTS      (      SELECT C.ParentID
                              FROM Child AS C
                              WHERE      (            (            C.ChildName      = 'Bob'
                                                            AND C.ChildAge      < 10
                                                      )
                                                OR      (            C.ChildName      = 'Chris'
                                                            AND C.ChildGender < 'Male'
                                                      )
                                          )
                                    AND            p.ParentID = C.ParentID
                        )
0
CL0V3RAuthor Commented:
Please keep this question open as I have not had an opportunity to test the suggestions yet.

Thank you to everyone for your contributions.  I'll let you know my results!

Pete
0
CL0V3RAuthor Commented:
Thank you!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.