subquery problem - 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)

subquery problem

Author

Topic

HalaszJ
Yak Posting Veteran

59 Posts

Posted - 2010-01-29 : 09:26:38

I have a query that I have joined to a request table, in that table I have a requestID and an itemID

My query in a simple form would be

Select
o.organization
, b.priority
, d.department
,
(
select
isnull(requestid, '0')
from
requests r
where
r.requestdate =
(select max(requestdate)
from
requests
where
requestid = r.requestdate)

The request table can have more than 1 request for the same item, to put it into terms, say an item is a conference room, and you request to have it tomorrow, once you get it, it is a completed request, so i only want to get the latest request

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2010-01-29 : 15:26:29

maybe this


Select 
o.organization
, b.priority
, d.department
, 
(
select
isnull(requestid, '0')
from
requests r
where
r.requestdate = 
(select top 1 requestdate
from
requests
where
requestid = r.requestid order by requestdate desc)

<><><><><><><><><><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-30 : 00:56:14

sorry i dont think you've posted full query. can you either post full query or post sample data and your reqd o/p? i think latter will be more helpful though

HalaszJ
Yak Posting Veteran

59 Posts

Posted - 2010-01-30 : 03:37:11

here is the table structure

requestTable
------------
requestGUID int (pk)
roomGUID uniqueidentifier (fk)
roomNumber int
OrganizationGUID uniqueidentifier (fk)
requestComplete datetime
Active bit

sample data
------------
1, 000000-0000-0000-0000-000000000000, 1, 000000-0000-0000-0000-000000000000, 2010-01-28 14:02:01.103, 0
2, 000000-0000-0000-0000-000000000000, 1, 000000-0000-0000-0000-000000000000, 2010-01-28 14:02:01.103, 1

the fk's just get me the name of the organization that needs the room, and the room name.

If you see, I have a room filled date, if that date is the most current and the active = 1, then that is the record i want, if the most current has an active = 0 then I just want to return 000000-0000-0000-0000-000000000000 as the guid

so my statemet starts out

select
isnull(requestid, "000000-0000-0000-0000-000000000000")

HalaszJ
Yak Posting Veteran

59 Posts

Posted - 2010-01-30 : 08:46:46

I took the cheap way out and dumped it all into a temp table and selected it out of there.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources