SQL Help - Multiple Table Joins

I thought I had this figured out with the help of the experts here but I discovered I didn't.  Here is the recap.  I have 3 Tables: A, B, C.  TableA and TableB have a one->one relationship.  TableA and TableC have a one->many relationship.  What I'm looking for is  query that will return all the fields inTableA, TableB, TableC but will return the most recent record in TableC.  I thought I had it worked out with the following SQL:

SELECT p.IDA, a.Field1, c.IDC, c.IDA, c.Field2, b.IDB, b.IDA, b.Field3
FROM TableA a
INNER JOIN TableB b
ON a.IDA = b.IDA
INNER JOIN (SELECT TOP 1 *
                  FROM TableC, TableA
                  WHERE TableC.IDA=TableA.IDA
                  ORDER BY TableC.IDC DESC) c
ON b.IDA = c.IDA

The problem is it only returns 1 record.  TableA contains many records and I'm only seeing the last record.  Can someone help me fix my query?  Any help is greatly appreciated!
dyaroshAsked:
Who is Participating?
 
lwadwellCommented:
try:
SELECT p.IDA, a.Field1, c.IDC, c.IDA, c.Field2, b.IDB, b.IDA, b.Field3
FROM TableA a
INNER JOIN TableB b
ON a.IDA = b.IDA
INNER JOIN (SELECT *, row_number() over(partition by IDA order by IDC desc) rn
              FROM TableC) c
ON b.IDA = c.IDA AND c.rn = 1

Open in new window

0
 
Christopher KileCommented:
OK, A and B have a one-to-one:

SELECT 
*
FROM TableA a
INNER JOIN TableB b
ON a.IDA = b.IDA

Open in new window


A and C have a one-to-many:

SELECT 
*
FROM TableA a
INNER JOIN TableC C
ON a.IDA = C.IDA

Open in new window


Now, if c.IDA is the link, and c.IDC is the ordering element, this is the table of record keys you're looking for (assuming (IDA, IDC) is a unique key on C):

SELECT 
C.IDA,
MAX(C.IDC)
FROM TableC
GROUP BY C.IDA

Open in new window


Join this back to C:

SELECT
C.*
FROM
(SELECT C.IDA, MAX(C.IDC) FROM TableC GROUP BY C.IDA) AS CMAX
INNER JOIN
TableC AS C
ON
C.IDA = CMAX.IDA
AND
C.IDC = CMAX.IDC

Open in new window


This gives the full row for each C.IDA where C.IDC is maximum (i.e. latest).

Join this back to A and B (see first code block):

SELECT 
*
FROM 
TableA a
INNER JOIN 
TableB b
ON 
a.IDA = b.IDA
INNER JOIN
(
SELECT
C.*
FROM
(SELECT C.IDA, MAX(C.IDC) FROM TableC GROUP BY C.IDA) AS CMAX
INNER JOIN
TableC AS C
ON
C.IDA = CMAX.IDA
AND
C.IDC = CMAX.IDC
) AS CFILTERED
ON
A.IDA = CFILTERED.IDA

Open in new window


If this runs a bit slow for you, consider building table variables and joining those to A * B.
0
 
dyaroshAuthor Commented:
Thank you.
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.

All Courses

From novice to tech pro — start learning today.