Help to extract these records - 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 2000 Forums

Transact-SQL (2000)

Help to extract these records

Author

Topic

Pinto
Aged Yak Warrior

590 Posts

Posted - 2006-04-20 : 06:08:22

I have a table, tblFiles which has a field STATUS. STATUS can be IN, OUT or REQ. There is also a LASTDATEOUT field.

I have another table tblFileRequestDetails which has an entry for every file requested, so some files may be on this table more than once. There are fields DATEOUT and DATEIN. If both are completed then that request has been completed.

I need to select all the files from tblFiles where the STATUS is OUT and then select the entry for that file from tblFileRequestDetails where DATEIN is empty.

The tables are linked by ID (tblFiles key) and tblFilesId (field on tblFileRequestDetails)

It sounds easy but I can't sort out the SQL. I have tried in a view.

Any help gratefully received.

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-04-20 : 06:30:09

Hi Pinto,

Try...

select * from tblFileRequestDetails
where tblFilesId in (select ID from tblFiles where status = 'OUT') and DATEIN is null

...or...

select *
from tblFileRequestDetails r inner join tblFiles f on r.tblFilesId = f.ID
where f.status = 'OUT' and r.DATEIN is null

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

Pinto
Aged Yak Warrior

590 Posts

Posted - 2006-04-20 : 09:18:06

Thanks for your reply Ryan. I have run into a problem. Some of the records in tblFileRequestDetails with the same tblFilesId do not have a DateIn although there is more than one entry. (I would guess due to users not keeping the database up to date). In these cases I need the latest DATEOUT. I tried amending your SQL as below but but it keeps setting the * as COUNT

SELECT TOP 100 PERCENT tblFilesId, MAX(DateOut) AS Expr1, COUNT(*) AS Expr2
FROM dbo.tblFileRequestDetails
WHERE (DateIn IS NULL)
GROUP BY tblFilesId
HAVING (tblFilesId IN
(SELECT ID
FROM tblFiles
WHERE status = 'OUT'))
ORDER BY tblFilesId

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources