fetching data from HISTORY table with joins

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

fetching data from HISTORY table with joins

Author

Topic

safecoder@gmail.com
Starting Member

22 Posts

Posted - 2006-06-07 : 00:33:54

groupies..thanks for ur time on this..but here is my dilemma..need ur valuable suggestion..I have a "vehicle" table and a "status history" table with vehicle ID as a forecign key..

Here i need to write a query to fetch all vehicleID's and latest status for that vehicleID from "status history" table..And if the "status history" table doesn't have a partcular vehicleId, i still want to return (vehicleID,NULL)..

Here is the query that i'm workig on now..

SELECT
vehicle.[vehicleID] AS VehicleId ,
status.[statusName] AS Status
from
[Vehicle] vehicle left join statusHistory status on
vehicle.VehicleID = status.VehicleID

but, the problem, is..it is getting all the previous statuses for that VehicleID from history table...

so, i added the following condition..

SELECT
vehicle.[vehicleID] AS VehicleId ,
status.[statusName] AS Status
from
[Vehicle] vehicle left join statusHistory status on
vehicle.VehicleID = status.VehicleID
where
status.sys_add_tmstmp = ( SELECT MAX(sys_add_tmstmp) FROM statusHistory WHERE vehicleID = @vehicle.VehicleID)

but, then..It is giving me a subset of records where VehicleId exists in the history table..

Am stuck @ this point...Do, i need to use a CURSOR to get the "LATEST" status for each vehicleID????

Any help/pointer/advice will be greatly appreciated..

Thanks,

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-06-07 : 00:39:51

Try this


SELECT vehicle.[vehicleID] AS VehicleId ,
       status.[statusName] AS Status
from   [Vehicle] vehicle left join statusHistory status 
on     vehicle.VehicleID     = status.VehicleID
and    status.sys_add_tmstmp = ( SELECT MAX(sys_add_tmstmp) FROM statusHistory hist WHERE hist.vehicleID = status.VehicleID)

safecoder@gmail.com
Starting Member

22 Posts

Posted - 2006-06-09 : 01:10:20

sweet...that works...why didn't i think of this earlier...hmm..

thanks a ton for ur time..

quote:

Originally posted by khtan

Try this


SELECT vehicle.[vehicleID] AS VehicleId ,
       status.[statusName] AS Status
from   [Vehicle] vehicle left join statusHistory status 
on     vehicle.VehicleID     = status.VehicleID
and    status.sys_add_tmstmp = ( SELECT MAX(sys_add_tmstmp) FROM statusHistory hist WHERE hist.vehicleID = status.VehicleID)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources