How do i use a nested select in a join?

[James]

Im trying to figure how how to return order status info. i.e. OrderID,
CurrentStatus, CompletedDate, DeliveredDate. i have an order table and a
statuslog table. the status log table is like this. OrderID, Status,Date. it
can log a history of differnt status's i.e. Order Received, Completed,
Delivered, etc. How can i return one record that shows the OrderID,
CurrentStatus, CompletedDate, DeliveredDate. obvoulsy if the order doesn't
have a completed or delivered entry it can't show the date, but i still need
it to show the record and the current status. below is a sql i have that
works to show the current status. but how can i get it to show the
CompletedDate and/or the DeliveredDate? would a nested select in a join help
me? i don't know how to do those.

SELECT sl.OrderID, sl.Status
FROM Statuslog AS sl
WHERE (((sl.Date)=(SELECT Max(sl2.date)
FROM statuslog As sl2
WHERE sl2.orderid = sl.orderid)));

 

[James]

i figured out how to nested selects in joins. but its slower than my orginal
sql i posted. is there a way to speed up this join sql and also display
CompletedDate and DeliveredDate?

SELECT s.OrderID, s.Status, s.Date
FROM Statuslog AS s INNER JOIN [select orderid, max(date) as MaxOfDate from
statuslog group by orderid]. AS s2 ON s.OrderID = s2.orderid;

 

[James]

nm, i didn't need to use joins at all. i just needed to put the nested
selects as columns. works must faster than a join because im limiting the
nested select by the parent selects primary key. i.e.

SELECT sl.OrderID AS OrderID, sl.Status AS Status, sl.Date, (select
max(s.date) as MaxOfDate from statuslog as s where s.status = "Completed"
and s.OrderID = sl.OrderID) as CompletedDate, (select max(s.date) as
MaxOfDate from statuslog as s where s.status = "Delivered" and s.OrderID =
sl.OrderID) as DeliveredDate
FROM Statuslog AS sl
WHERE (((sl.Date)=(SELECT Max(sl2.date)
FROM statuslog As sl2
WHERE sl2.orderid = sl.orderid)));

i figured out how to nested selects in joins. but its slower than my
orginal sql i posted. is there a way to speed up this join sql and also
display CompletedDate and DeliveredDate?

SELECT s.OrderID, s.Status, s.Date
FROM Statuslog AS s INNER JOIN [select orderid, max(date) as MaxOfDate
from statuslog group by orderid]. AS s2 ON s.OrderID = s2.orderid;

Im trying to figure how how to return order status info. i.e. OrderID,
CurrentStatus, CompletedDate, DeliveredDate. i have an order table and a
statuslog table. the status log table is like this. OrderID, Status,Date.
it can log a history of differnt status's i.e. Order Received, Completed,
Delivered, etc. How can i return one record that shows the OrderID,
CurrentStatus, CompletedDate, DeliveredDate. obvoulsy if the order
doesn't have a completed or delivered entry it can't show the date, but i
still need it to show the record and the current status. below is a sql i
have that works to show the current status. but how can i get it to show
the CompletedDate and/or the DeliveredDate? would a nested select in a
join help me? i don't know how to do those.

SELECT sl.OrderID, sl.Status
FROM Statuslog AS sl
WHERE (((sl.Date)=(SELECT Max(sl2.date)
FROM statuslog As sl2
WHERE sl2.orderid = sl.orderid)));