SQL Join 3 tables - Max date

[TMEAGLE2]

Hi.. I'm kind of new to SQL programming. My environment is using Dreamweaver to access a IBM DB2 DB - basically using the recordset builder to create SQL. I have three tables:

Order Table
Order_Number
Truck_Number

Mobile Table
Device_ID
Assigned_Truck

Location Table
Device
Location_Time
Longitude
Latitude

What I would like to do is show the ORDER NUMBER, ASSIGNED TRUCK, and the most recent (max of) LOCATION_TIME and the LONGITUDE and LATITUDE at that max time.

The relationships are as follows:

Location_Table.Device = Mobile_Table.Device_ID
Mobile_Table.Assigned_Truck = Order_Table.Truck_Number

Can anyone give me some guidance? I can join 2 of the 3 tables.. but that 3rd join is hanging me up. Any assistance would be greatly appreciated.

 

[markros]

Please check this blog Including an Aggregated Column's Related Values

Your solution may be

select A.*, B.*, C.* from Order_Table A inner join Mobile_Table B on A.Truck_Number = B.Assigned_Truck INNER JOIN Location Table C on B.Device_ID = C.Device where C.PkField = (select top 1 PkField from Location_Table order by Location_Time DESC where Location_Table.Device = B.Device_ID)