Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 356
  • Last Modified:

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!
0
dyarosh
Asked:
dyarosh
1 Solution
 
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
 
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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
 
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
 
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

Featured Post

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

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