Complicated Age range Query

[Gdyson]

This looks pretty simple on the face of it but I can't get my head around it.

I have two tables that I'm querying:

dbo.tblrespondents
RespondentID
FirstName
Surname

dbo.tblrespondentschildren
RespondentsChildID
RespondentID
ChildFirstName
ChildSurname
ChildDOB
ChildSex

I am trying to find which Respondents who have only sons - one must be aged between 01/09/1998 and 31/08/1999 and another aged between 01/09/1995 and 08/12/1995.

The query I'm using is:

SELECT r.FirstName, r.Surname
from dbo.tblRespondents r
INNER JOIN dbo.tblRespondentsChildren rc
on r.RespondentID = rc.RespondentID
where rc.Dob between ('1998-09-01') and ('1999-08-31')
and rc.Dob between ('1995-09-01') and ('1995-12-08')
and rc.SexID = 1

It's not returning any results which seems odd to me, knowing the data. I think it's failing because it's trying to find ONE child between 2 age ranges whereas it should be finding Respondents who have at least 2 children, one between each age range.

[darkagn]

You need to join to the tblRespondentsChildren table twice, once for each child.
Something like this should give you a start:

SELECT r.FirstName, r.Surname FROM tblRespondents r
INNER JOIN tblRespondentsChildren rc1 ON r.RespondentID = rc1.RespondentID
INNER JOIN tblRespondentsChildren rc2 ON r.RespondentID = rc2.RespondentID
WHERE rc1.DOB BETWEEN ('1998-09-01') AND ('1999-08-31')
AND rc2.DOB BETWEEN ('1995-09-01') AND ('1995-12-08')
AND rc1.SexID = 1
AND rc2.SexID = 1