Select subquery - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Select subquery

Author

Topic

kdeutsch
Yak Posting Veteran

83 Posts

Posted - 2009-05-14 : 12:48:50

HI when I do a sub-select in a sql query it does not pull the corret data for some reason. Here is the inital code that pulls 14 records which is correct with test dates. Then what I need to do is add a sub select that checks another table to make sure one of the records does not have a dtExpire date of greater than today or a dtCancelACn date. If they have a dtEpire date of greater than today they should not show up but if they have a dtCancelACN date in that same table they should show up. So the first code is without the additional where clause statements, works fine. But when I add teh rest it never pulls correct infor no matter how I change the and, or statements.

Select r.intRecruitID,
r.strLastName + ', ' + r.strFirstName + ', ' + r.strMiddlename as Fullname,
r.strSSN,
r.dtProcessing,
r.strMOS,
m.strMeps,
r.strAFQT
from tblRecruit as r Inner Join
tblMeps as m on m.intMepsId = r.intMepsId
Where (r.dtProcessing >= dateadd(d, datediff(d, 0, '10/7/2002 12:00:00 AM'),4) and r.dtProcessing < dateadd(d, datediff(d, 0, '10/7/2002 12:00:00 AM'),5)) or (r.intRecruitId in (select intRecruitId from tblACNRequest where dtExpire <= getdate()))

now with added clauses

Select r.intRecruitID,
r.strLastName + ', ' + r.strFirstName + ', ' + r.strMiddlename as Fullname,
r.strSSN,
r.dtProcessing,
r.strMOS,
m.strMeps,
r.strAFQT
from tblRecruit as r Inner Join
tblMeps as m on m.intMepsId = r.intMepsId
Where r.dtProcessing >= dateadd(d, datediff(d, 0, '10/7/2002 12:00:00 AM'),4) and r.dtProcessing < dateadd(d, datediff(d, 0, '10/7/2002 12:00:00 AM'),5) and (r.intRecruitId in (Select intRecruitId from tblACNRequest where dtExpire <= getdate()) or
(r.intRecruitId in (select intRecruitId from tblACNRequest where dtCancelACN <= getdate())))
Order by strLastName

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-14 : 14:08:51

without seeing data, we cant really understand your scenario. can you post some sample data and then explain what you're looking for as output?

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2009-05-14 : 14:12:16

[code]
Select r.intRecruitID,
r.strLastName + ', ' + r.strFirstName + ', ' + r.strMiddlename as Fullname,
r.strSSN,
r.dtProcessing,
r.strMOS,
m.strMeps,
r.strAFQT
from
tblRecruit r
Inner Join
tblMeps m
on m.intMepsId = r.intMepsId
Where
(
r.dtProcessing >= dateadd(d, datediff(d, 0, '10/7/2002 12:00:00 AM'),4)
and r.dtProcessing < dateadd(d, datediff(d, 0, '10/7/2002 12:00:00 AM'),5)
)
or
(
exists (select *
from tblACNRequest aa
where aa.dtExpire <= getdate() and aa.IntRecruitID = r.IntRecruitID
)
and exists (select *
from tblACNRequest aa
where aa.dtCANCELACN <= getdate() and aa.IntRecruitID = r.IntRecruitID
)
)
[/code]

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

kdeutsch
Yak Posting Veteran

83 Posts

Posted - 2009-05-14 : 16:06:00

Ok this will probably help,
If I run this amount of code I get 14 people, which is what I should get.

Select r.intRecruitID,
r.strLastName + ', ' + r.strFirstName + ', ' + r.strMiddlename as Fullname,
r.strSSN,
r.dtProcessing,
r.strMOS,
m.strMeps,
r.strAFQT
from tblRecruit as r Inner Join
tblMeps as m on m.intMepsId = r.intMepsId
Where r.dtProcessing >= dateadd(d, datediff(d, 0, '10/7/2002 12:00:00 AM'),4)
and r.dtProcessing < dateadd(d, datediff(d, 0, '10/7/2002 12:00:00 AM'),5)

Depending on the where clasue from this point I want to add or subtract people.
Here is what i get when I query all from tblACNRequest.
IntACNId strACNNUm intRecruitId dtExpire dtCancelACN
1 2009134-51012 40 2009-05-20 00:00:00.000 2009-05-14 14:11:04.000
2 2009134-51036 39 2009-05-20 00:00:00.000 NULL
3 2009134-51059 78 2009-05-20 00:00:00.000 2009-05-14 14:11:05.000
4 2009134-51087 24 2009-05-20 00:00:00.000 NULL

so From teh 14 records above I have 2 that are active and 2 that are active cancelled.
So I don't want to show the 2 active records other wise they would be duplicated, but I do want to show the
2 people who's records where cancelled. So showing up in my query would be 12 people.

So if I run this.

Select r.intRecruitID,
r.strLastName + ', ' + r.strFirstName + ', ' + r.strMiddlename as Fullname,
r.strSSN,
r.dtProcessing,
r.strMOS,
m.strMeps,
r.strAFQT
from tblRecruit as r Inner Join
tblMeps as m on m.intMepsId = r.intMepsId
Where r.dtProcessing >= dateadd(d, datediff(d, 0, '10/7/2002 12:00:00 AM'),4)
and r.dtProcessing < dateadd(d, datediff(d, 0, '10/7/2002 12:00:00 AM'),5) and
(intRecruitId in (Select intRecruitId from tblACNRequest where dtExpire <= getdate()))
Order by strLastName

It should pull up 10 records of people whom have not been submitted yet but pulls up nothing.

Then I add in the final statement and it also pulls up nothing but shoule pull up the 12 records,
10 that where not processed + 2 that where cancelled.

and r.intRecruitId in(Select intRecruitId from tblACNRequest where dtCancelAcn <=getdate()))

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-05-14 : 16:51:59

Without sample data and expected output we are kind of grabbing at straws, please see this link:
[url]http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx[/url]

Myabe this will help you towards a solution???

Select 
r.intRecruitID, 
r.strLastName + ', ' + r.strFirstName + ', ' + r.strMiddlename as Fullname, 
r.strSSN, 
r.dtProcessing, 
r.strMOS,
m.strMeps, 
r.strAFQT 
from 
	tblRecruit as r 
Inner Join 
	tblMeps as m 
	on m.intMepsId = r.intMepsId 
	AND r.dtProcessing >= dateadd(d, datediff(d, 0, '10/7/2002 12:00:00 AM'),4) 
	and r.dtProcessing < dateadd(d, datediff(d, 0, '10/7/2002 12:00:00 AM'),5) 
LEFT OUTER JOIN
	tblACNRequest as Req
	ON r.intRecruitId  = Req.intRecruitId 
	AND 
	(
		Req.dtExpire <= getdate()
		OR dtCancelAcn <=getdate()
	)
WHERE
	Req.intRecruitId IS NOT NULL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources