I have tricky query.. I have following 4 tables
tblOrder, tblOrderDetails, tblOrderStatus, tblStatus
tblOrder has single field (primarikey) : OrderId
tblOrderDetails contains - DetailId, OrderId(FK), ValueType, Value
tblOrderStatus contains - OrderId, StatusId, StatusDateTime, UserId (to see who has changed the status last)
tblStatus contains - StatusId, StatusValue.
Now the requirement is
I can have more than 1 values in tblOrderDetails & tblOrderStatus.
Now while retrieving the data I want to archive the Latest tblOrderStatus record for that orderId.
Lets say for OrderId=1 I have 3 records in tblOrderStatus
One for Intianliy placed order (Require confirmation)
Second is (Order Confirmed)
third is (Order Completed)
Now when I see the order date, I want to get all the values of tblOrderStatus. I want following columns
OrderId, StatusId, StatusValue, StatusDateTime, UserId.
so for every order I can have its latest statusvalue..
How can I do this? Please help.