I thought this would be an easy query - but it isn't.

This is a support call tracking app. that we have running here. We have support calls and notes that are continually added to them throughout the day. I want to be able to query the list of open support calls along with the information on the last support call note. I can do it by using my web code (ColdFusion in case anyone cares) to loop queries, but that means running tons and tons of queries. I'd really like to return the results in a single query. Here's what I have so far:

Tables:

tblSupportCalls
SupportCallID
EmployeeID
SupportCallDesc (Not needed in this query.)
SupportCallStatus

tblSupportCallNotes
SupportCallNoteID
SupportCallID
EmployeeID
SupportCallNoteTime
SupportCallNoteDesc (Not needed in this query.)

The problem with this query is that it is returning multiple records and I can't figure out why. I don't know if this is important, but we are using SQL Server 2000.

Thanks in advance for anyone who can shed a little light on my problem.

—Supes

Try something like below (I have no MS SQL Server to try, but it should work):

SELECT
*
FROM
tblSupportCalls calls
left outer join
(
SELECT notes.*
FROM
tblSupportCallNotes notes,
(SELECT SupportCallID, MAX(SupportCallNoteTime) SupportCallNoteTime
FROM tblSupportCallNotes
GROUP BY SupportCallID) lastNote
WHERE
notes.SupportCallID = lastNote.SupportCallID
AND notes.SupportCallNoteTime = lastNote.SupportCallNoteTime
) notes
ON notes.SupportCallID = calls.SupportCallID
WHERE
calls.SupportCallStatus = 0
ORDER BY calls.SupportCallID DESC


In general I would suggest to add a column to tblSupportCalls and track the last tblSupportCallNotes

Simply amazing! It worked perfectly. I can't thank you enough.

I'll be honest, though: I can't quite follow just *what* you did. Is there possibly some resource (book, web site) that can explain it to me?

My most humble thanks.

—Supes

The idea is pretty basic

1. "create table" that represents last notice time per call (lastNote)
2. Join the result back to notice table to get last notice information, effectively selecting only one notice per call
3. left outer join between calls and last notices (call may have no notices)