Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 343
  • 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
Build your data science skills into a career

Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.

 
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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

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