• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 347
  • Last Modified:

Joining a table to the latest matching row of another table

I have two tables, SupportCall and CallHistory. The CallHistory table records the conversations that happen for a support call. I want a query that returns columns for a specified SupportCall, plus columns from the latest CallHistory row. CallHistory has an IDENTITY column HistoryID. How do I join the tables in a single query?

A column SupportCallID joins the tables. CallHistory has columns HistoryID, SupportCallID, ConversationDate, and ConversationDetails.
0
crescendo
Asked:
crescendo
  • 3
  • 2
1 Solution
 
jdlambert1Commented:
SELECT sc.*, ch.*
FROM SupportCall sc INNER JOIN CallHistory ch ON sc.SupportCallID = ch.SupportCallID
ORDER BY SupportCallID, HistoryID
0
 
HilaireCommented:
SELECT TOP 1 a.*, b.*
FROM SupportCall a INNER JOIN CallHistory b ON a.SupportCallID = b.SupportCallID
WHERE a.SupportCallID = @aSpecifiedSupportCall
ORDER BY HistoryID desc
0
 
jdlambert1Commented:
>for a specified SupportCall

SELECT sc.*, ch.*
FROM SupportCall sc INNER JOIN CallHistory ch ON sc.SupportCallID = ch.SupportCallID
WHERE SupportCallID = 123
ORDER BY HistoryID
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
HilaireCommented:
If you need it for all support callls

SELECT a.*, c.*
FROM SupportCall a
INNER JOIN (
   select SupportCallID, max(HistoryId) as HistoryID from CallHistory group by SupportCallID
) b ON a.SupportCallID = b.SupportCallID
INNER JOIN CallHistory c on b.HistoryID = c.HistoryID
0
 
jdlambert1Commented:
Ah, you got it right the first time, Hilaire, crescendo asked for the latest CallHistory row.
0
 
crescendoAuthor Commented:
Thanks to everyone for the amazingly quick response. As usual, Hilaire has an elegant solution.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now