asked on

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

ASKER CERTIFIED SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

APoPhySpt

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

CL0V3R

ASKER

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

APoPhySpt

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!

penyCuicas

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
                        )

CL0V3R

ASKER

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

CL0V3R

ASKER

Thank you!