Solved

Joining a table to the latest matching row of another table

Posted on 2004-09-30
6
284 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
Comment Utility
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
Comment Utility
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
Comment Utility
>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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 26

Expert Comment

by:Hilaire
Comment Utility
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
Comment Utility
Ah, you got it right the first time, Hilaire, crescendo asked for the latest CallHistory row.
0
 
LVL 9

Author Comment

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

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

In this article—a derivative of my DaytaBase.org blog post (http://daytabase.org/2011/06/18/what-week-is-it/)—I will explore a few different perspectives on which week today's date falls within using Microsoft SQL Server. First, to frame this stu…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

728 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now