Help Me Write This Query

Sorry for my newbieness:
I have a table (Cases) where Case_Number is the unique id field.
Another table (CommentsTable) contains these fields
Case_Number
Comment_Date
Comment
As users enter comments to the database, the comment gets a date/time
stamp (Comment_Date) and is recorded in the Comment field, and the case
number for that comment is also recorded.
I want my query to return fields from the Cases table, but also return
the last comment from the CommentsTable. I know I need to use the
Case_Number and MAX(Comment_Date) then return the Comment for that max
date and that case number, and I'm pretty sure this will be a sub
query, I just can't figure out how to put it all together.
Could somebody get me going in the right direction?
TIA,
todTod wrote:
> Sorry for my newbieness:
> I have a table (Cases) where Case_Number is the unique id field.
> Another table (CommentsTable) contains these fields
> Case_Number
> Comment_Date
> Comment
>
select * from CommentsTable ct where not exists(select 1 from
CommentsTable ct1
where ct.Case_Number = ct1.Case_Number and ct.Comment_Date <
ct1.Comment_Date)|||Tod wrote:
> Sorry for my newbieness:
> I have a table (Cases) where Case_Number is the unique id field.
> Another table (CommentsTable) contains these fields
> Case_Number
> Comment_Date
> Comment
> As users enter comments to the database, the comment gets a date/time
> stamp (Comment_Date) and is recorded in the Comment field, and the case
> number for that comment is also recorded.
> I want my query to return fields from the Cases table, but also return
> the last comment from the CommentsTable. I know I need to use the
> Case_Number and MAX(Comment_Date) then return the Comment for that max
> date and that case number, and I'm pretty sure this will be a sub
> query, I just can't figure out how to put it all together.
> Could somebody get me going in the right direction?
> TIA,
> tod
Untested:
SELECT C.Case_Number, T.Comment_Date, T.Comment
FROM dbo.Cases AS C
JOIN dbo.CommentsTable AS T
ON C.Case_Number = T.Case_Number
WHERE T.Comment_Date =
(SELECT MAX(Comment_Date)
FROM dbo.CommentsTable
WHERE Case_Number = T.Case_Number);
Unless (Case_Number, Comment_Date) is unique in the CommentsTable you
could still get more than one row per Case_Number.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||This might be neater if you are returning a single case. The Case_Number is
stored in the @.CaseNumber variable in this example:
SELECT TOP 1 C.Case_Number, T.Comment_Date, T.Comment
FROM dbo.Cases C
INNER JOIN dbo.CommentsTable T ON C.Case_Number = T.Case_Number
WHERE T.Case_Number = @.CaseNumber
ORDER BY T.Comment_Date DESC
If you wish to remove time adjustments from the equation (e.g. daylight
saving adjustments) then you could add an IDENTITY column to the
CommentsTable table (Comment_ID) and use the following:
SELECT TOP 1 C.Case_Number, T.Comment_Date, T.Comment
FROM dbo.Cases C
INNER JOIN dbo.CommentsTable T ON C.Case_Number = T.Case_Number
WHERE T.Case_Number = @.CaseNumber
ORDER BY T.Comment_ID DESC
Chris
"David Portas" wrote:

> Tod wrote:
> Untested:
> SELECT C.Case_Number, T.Comment_Date, T.Comment
> FROM dbo.Cases AS C
> JOIN dbo.CommentsTable AS T
> ON C.Case_Number = T.Case_Number
> WHERE T.Comment_Date =
> (SELECT MAX(Comment_Date)
> FROM dbo.CommentsTable
> WHERE Case_Number = T.Case_Number);
> Unless (Case_Number, Comment_Date) is unique in the CommentsTable you
> could still get more than one row per Case_Number.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>|||Thanx. I think this is closest to what I need.
Chris Howarth wrote:
> This might be neater if you are returning a single case. The Case_Number i
s
> stored in the @.CaseNumber variable in this example:
> SELECT TOP 1 C.Case_Number, T.Comment_Date, T.Comment
> FROM dbo.Cases C
> INNER JOIN dbo.CommentsTable T ON C.Case_Number = T.Case_Number
> WHERE T.Case_Number = @.CaseNumber
> ORDER BY T.Comment_Date DESC
>
> If you wish to remove time adjustments from the equation (e.g. daylight
> saving adjustments) then you could add an IDENTITY column to the
> CommentsTable table (Comment_ID) and use the following:
> SELECT TOP 1 C.Case_Number, T.Comment_Date, T.Comment
> FROM dbo.Cases C
> INNER JOIN dbo.CommentsTable T ON C.Case_Number = T.Case_Number
> WHERE T.Case_Number = @.CaseNumber
> ORDER BY T.Comment_ID DESC
> Chris
>
> "David Portas" wrote:
>