• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 267
  • Last Modified:

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
0
CL0V3R
Asked:
CL0V3R
1 Solution
 
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
 
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
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now