SQL Query Latest Tracking details
-
Please help me with this SQL Query
Table1 has id, name and tracking_number. Table2 has id, tracking_number, start_date and user_id. Table3 has user_id, firstname, lastname.
Table1 has records in it that may or maynot have tracking_numbers. There is a 0...n association between Table1 and Table2. There is a 1-1 relationship between Table2 and Table3.
I need to list out the Table1 records along with the latest Fields in Table2 (start_date is the max) along with the associated user fileds in Table3.
Table1 1, RECORD1, TR001
2, RECORD2, null
Table2 1, TR001, 01/01/2001, user1
2, TR001, 01/02/2001, user2
Table3 user1, Tim Brown
user2, John Green
The result need to look like
1 RECORD1 TR001 01/02/2001 John Green
2 RECORD2 null null null
Thanks for the help
A-
-
try this
Create table Table1(
id
int,
name
varchar(100),
tracking_number
char(5)
)
Create
table Table2
(
id
int,
tracking_number
char(5),
start_date
datetime,
userid
char(5)
)
Create
table Table3
(
userid
char(5),
first_name
varchar(50),
last_name
varchar(50)
)
insert
into Table1 values(1, 'RECORD1', 'TR001')
insert
into Table1 values( 2, 'RECORD2', null)
insert
into Table2 values( 1, 'TR001', '01/01/2001', 'user1' )
insert
into Table2 values( 2, 'TR001', '01/02/2001', 'user2')
insert
into Table3 values( 'user1', 'Tim','Brown')
insert
into Table3 values( 'user2', 'John', 'Green')
select
a.id,a.name,a.tracking_number,a.start_date,c.first_name,c.last_name
from
(
select a.id,a.name,a.tracking_number,max(b.start_date) as start_date,max(b.userid) as userid
from
Table1 a left join Table2 b
on
a.tracking_number=b.tracking_number
Group
by a.id,a.name,a.tracking_number
)
a
left
join Table3 c
on
a.userid=c.userid
-
Gopi. Thanks for your response. Your solution worked!
Thanks a lot
Ash-
-
There is a small issue with the query. The inner query "(select a.id,a.name,a.tracking_number,max(b.start_date) as start_date,max(b.userid) as userid from Table1 a left join Table2 b" ---- will give incorrect value for the userid. The query should not be returning the Max user_id. The query should be returning the user_id value where the row = max(start_date)
-
try this
select
a.id,a.name,a.tracking_number,a.start_date,c.first_name,c.last_name
from
(
select a.id,a.name,a.tracking_number,max(b.start_date) as start_date,max(c.userid) as userid
from Table1 a left join Table2 b
on a.tracking_number=b.tracking_number
left join
(
select userid from Table2 where start_date=(select max(start_date) as start_date from Table2 Group by tracking_number)
) c
on b.userid=c.userid
Group by a.id,a.name,a.tracking_number
)
a
left
join Table3 c
on
a.userid=c.userid
-
First of all thank you for your help.
This solution that you had proposed works partially. The query will return id, name, tracking_number and start_date for the record whose starting date is the latest. Each record in Table A may or may not have a tracking number.
Table1 1, RECORD1, TR001
2, RECORD2, null
3, RECORD2, TR002
Table2 1, TR001, 01/01/2001, user1
2, TR001, 01/02/2001, user2
3, TR002, 01/01/2006, user1
4, TR002, 01/02/2006, user1
5, TR002, 01/03/2006, user2
Table3 user1, Tim Brown
user2, John Green
The result need to look like
1 RECORD1 TR001 01/02/2001 John Green
2 RECORD2 null null null
3 RECORD3 TR002 01/03/2006 John Green
When i run the above mentioned query the result looks like this which is not what i need.
1 RECORD1 TR001 null null
2 RECORD2 null null null
3 RECORD3 TR002 01/03/2006 John Green
Thanks for your help
Ashok
-
I think you need to force the join order with something like:
select T1.[Name], T1.tracking_number, T2.start_date, T3.first_name, T3.last_name
from Table1 T1
left join
(
Table2 T2
join
( select T22.tracking_number, max(T22.start_date) as start_date
from Table2 T22
group by T22.tracking_number) D
on T2.tracking_number = D.tracking_number and T2.start_date = D.start_date
join Table3 T3 on T2.userid = T3.userid )
on T1.tracking_number = T2.tracking_number
-
This solution seems to be working for me.
Thank you!
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply