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

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!
0
dyarosh
Asked:
dyarosh
1 Solution
 
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
 
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
 
dyaroshAuthor Commented:
Thank you.
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

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