Select Only the most current record (SQL & ColdFusion) 1

[EastIndian]

This is a fine mess I've gotten myself in--any advice is most appreciated. I am creating a type of library to be accessed over the web (Cold Fusion). I have a book number, a book type (of which there are 2) and the options of either "checked in" or "checked out" for each book. Since one book's status can be changed many times over time, I need to be able to select only the current status for the book to be able to tell if it is checked in or out, while retaining the history of who has checked out the book and when.

I have a database with 4 relational tables (2 serve as combo box values). The query I am working with is as follows:

SELECT DISTINCT f.pkFldBook, f.FldBkNum, f.fkType, s.fkstatus, s.Datex, s.Name, s.fkFldBook, t.Type, t.pkType, c.pkStatus, c.Status, s.pkStatus
FROM tblFldBook f, tblStatus s, tblCboType t, tblCboStatus c
WHERE (f.FldBkNum LIKE '#form.FldBkNum#') AND (f.fkType = #form.Type#) AND (f.pkFldBook = s.fkFldBook) AND (f.fktype = p.pktype) AND (c.pkStatus = s.fkstatus)

This query returns the entire history for the book number chosen. In order to select only the most recent, I tried adding another AND statement to the query to select only the most recent status of the book:
AND (s.pkstatus = (SELECT MAX (s.pkstatus) from tblStatus s)

But, when I do this, I only get the results of the last record added to the table, not the last record of the exact book I am searching for.

Can anyone help?

 

[tlbroadbent]

Add criteria to identify the book in the sub-query.

AND s.pkstatus =
(SELECT MAX (pkstatus) from tblStatus
WHERE fkFldBook=f.pkFldBook)

I also recommend using ANSI JOIN Syntax as I have done below. Read the article at the following link to find out why.

http://www.microsoft.com/sql/using/tips/development/July23.asp

SELECT
f.pkFldBook, f.FldBkNum,
f.fkType, s.fkstatus,
s.Datex, s.Name, s.fkFldBook,
t.Type, t.pkType,
c.pkStatus, c.Status,
s.pkStatus
FROM tblFldBook f
JOIN tblStatus s
ON (f.pkFldBook = s.fkFldBook)
JOIN tblCboType t
ON (f.fktype = t.pktype)
JOIN tblCboStatus c
ON (c.pkStatus = s.fkstatus)

WHERE f.FldBkNum LIKE '#form.FldBkNum#'
AND f.fkType = #form.Type#

AND s.pkstatus =
(SELECT MAX (pkstatus) from tblStatus
WHERE fkFldBook=f.pkFldBook) Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions in the SQL Server forum. Many of the ideas apply to all forums.

 

[Tarwn]

How about adding a timestamp to the table. That way you can just select as you would above and then ORDER DESC by date, it will return all of the records with the latest one as the top of the recordset