Solved

Joining a table to the latest matching row of another table

Posted on 2004-09-30
6
323 Views
Last Modified: 2012-06-21
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
Comment
Question by:crescendo
  • 3
  • 2
6 Comments
 
LVL 15

Expert Comment

by:jdlambert1
ID: 12188288
SELECT sc.*, ch.*
FROM SupportCall sc INNER JOIN CallHistory ch ON sc.SupportCallID = ch.SupportCallID
ORDER BY SupportCallID, HistoryID
0
 
LVL 26

Accepted Solution

by:
Hilaire earned 250 total points
ID: 12188302
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
 
LVL 15

Expert Comment

by:jdlambert1
ID: 12188303
>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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 26

Expert Comment

by:Hilaire
ID: 12188323
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
 
LVL 15

Expert Comment

by:jdlambert1
ID: 12188331
Ah, you got it right the first time, Hilaire, crescendo asked for the latest CallHistory row.
0
 
LVL 9

Author Comment

by:crescendo
ID: 12188385
Thanks to everyone for the amazingly quick response. As usual, Hilaire has an elegant solution.
0

Featured Post

Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question