December 8, 2010 at 10:26 am
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.
December 8, 2010 at 10:30 am
You need to join twice:
SELECT
r.FirstName,
r.Surname
FROM
dbo.tblRespondents AS r
INNER JOIN dbo.tblRespondentsChildren AS rc_1
ON r.RespondentID = rc_1.RespondentID
AND rc_1.Dob BETWEEN ('1998-09-01') AND ('1999-08-31')
AND rc_1.SexID = 1
INNER JOIN dbo.tblRespondentsChildren AS rc_2
ON r.RespondentID = rc_2.RespondentID
AND rc_2.Dob BETWEEN ('1995-09-01') AND ('1995-12-08')
AND rc_2.SexID = 1;
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 8, 2010 at 10:33 am
And, actually, that'll give you possible row-multiplication if someone has, for example, twin boys on one of those dates and thus has two child rows that qualify.
This might be better:
SELECT
r.FirstName,
r.Surname
FROM
dbo.tblRespondents AS r
WHERE
EXISTS ( SELECT
1
from
dbo.tblRespondentsChildren AS rc_1
where
r.RespondentID = rc_1.RespondentID
AND rc_1.Dob BETWEEN ('1998-09-01') AND ('1999-08-31')
AND rc_1.SexID = 1 )
AND EXISTS ( SELECT
1
from
dbo.tblRespondentsChildren AS rc_2
WHERE
r.RespondentID = rc_2.RespondentID
AND rc_2.Dob BETWEEN ('1995-09-01') AND ('1995-12-08')
AND rc_2.SexID = 1 ) ;
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 9, 2010 at 2:31 am
Thanks, that last one was spot on.
December 13, 2010 at 6:52 am
You're welcome.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply