SQL Query Help

I have 3 tables that I need to join.  That part I can do.  What I don't know how to do is that one of the tables can have multiple records and what I need is the last record entered.  Here are the relavent table definitions:

TableA                       TableB                         TableC
-----------                       -----------                         ----------
IDA                            IDB                              IDC
Field1                        IDA                              IDA
Field2                        FieldA                          FieldZ

TableA and TableB will only have one record for IDA but TableC can have multple records with IDA.  I need the most recent record from TableC.

Here is the SQL that I use to Join the 3 tables.

SELECT TableA.*, TableB.*, TableC.*
FROM TableA
INNER JOIN TableB
ON TableA.IDA = TableB.IDA JOIN TablceC
ON TableB.IDA = TableC.IDA

Any help is greatly appreciated!
dyaroshAsked:
Who is Participating?
 
Ephraim WangoyaCommented:
Depends on what field defines your latest record, suppose its a date field, you could do this

SELECT A.*, B.*, C.*
FROM TableA A
INNER JOIN TableB B ON A.IDA = B.IDA 
INNER JOIN (select top 1 * from TablceC order by datefield desc) C ON B.IDA = C.IDA

Open in new window

0
 
Jared_SCommented:
You might be able to use row_number or rank.

Can you post any sample data for the table?
0
 
dyaroshAuthor Commented:
TableA
-----------
1, John, Doe

TableB
-----------
12, 1, PA

TableC
------------
1,1,English
2,1,Math
3,1,Lunch

The SQL that I am using will return:
1, John, Doe, 12, 1, PA, 1, 1, English
1, John, Doe, 12, 1, PA, 2, 1, Math
1, John, Doe, 12, 1, PA, 3, 1, Lunch

What I need is:
1, John, Doe, 12, 1, PA, 3, 1, Lunch
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
ZberteocCommented:
Use this:

SELECT 
	TableA.*, TableB.*, TableC.*
FROM 
	TableA
	INNER JOIN TableB
		ON TableA.IDA = TableB.IDA 
	JOIN TablceC
		ON TableB.IDA = TableC.IDA
		AND TableC.ID=(SELECT MAX(ID) FROM TableC WHERE IDA=TableA.IDA)

Open in new window


TableC has a column with values 1,2,3 which I assumed is called ID and the IDA is the value that identifies the person in all 3 tables. You need to add to the JOIN the fact that you need the MAX ID from those existing for an IDA value.
0
 
dyaroshAuthor Commented:
I tried your SQL and still received multiple records for a IDA.
0
 
ZberteocCommented:
What are the column names in your tables for the examples you gave?


TableA
-----------
1, John, Doe

TableB
-----------
12, 1, PA

TableC
------------
1,1,English
2,1,Math
3,1,Lunch

The SQL that I am using will return:
1, John, Doe, 12, 1, PA, 1, 1, English
1, John, Doe, 12, 1, PA, 2, 1, Math
1, John, Doe, 12, 1, PA, 3, 1, Lunch

If in all tables the IDA value for John is 1 and in Table C the name for column with 1,2,3 values is ID the query should work.
0
 
dyaroshAuthor Commented:
The ID fields are IDENTITY Fields therefore they are numeric.
0
 
ZberteocCommented:
You still didn't answer to my question about the column names. We don't have enough information and we can't go further.
0
 
dyaroshAuthor Commented:
It wasn't the solution but it led me to the solution which I'm postin here:

SELECT A.*, B.*, C.*
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.IDA DESC) C
ON A.IDA = C.IDA
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.