Add table(s) to query

[LadyLin]

I have this query

select t1.*,t2.opr,t2.resource_id,t2.PrevOp,t2.NextOp
from
 (select w.type, w.base_id, w.lot_id, w.split_id,w.sub_id, w.desired_want_date,w.user_7
 from work_order w where w.type = 'w' and
 (w.status = 'F' or w.status = 'R' or w.status = 'U')
 and w.base_id like 'AG%')t1
join
 (SELECT op1.WORKORDER_TYPE as type, op1.WORKORDER_BASE_ID as base_id, 
op1.WORKORDER_LOT_ID as lot_id,op1.WORKORDER_SPLIT_ID as split_id,
 op1.WORKORDER_SUB_ID as sub_id, op1.SEQUENCE_NO as opr, 
 op1.resource_id,
 ISNULL(
   (SELECT MAX(sequence_no)
     FROM operation
    WHERE workorder_type = 'W'  AND
    workorder_base_id = op1.workorder_base_id AND workorder_lot_id =
     op1.workorder_lot_id AND workorder_Split_id = op1.workorder_split_id
      AND Workorder_sub_id = op1.workorder_sub_id AND sequence_no <
      op1.sequence_no), - 1) AS PrevOp, 
 ISNULL(
  (SELECT MIN(sequence_no) 
    FROM operation
     WHERE workorder_type = 'W' 
     AND workorder_base_id = op1.workorder_base_id AND
     workorder_lot_id = op1.workorder_lot_id AND workorder_Split_id =
     op1.workorder_split_id AND Workorder_sub_id = op1.workorder_sub_id
     AND sequence_no > op1.sequence_no), - 1) AS NextOp
  FROM OPERATION OP1)t2

on t1.base_id = t2.base_id and t1.lot_id = t2.lot_id
   and t1.split_id = t2.split_id and t1.sub_id = t2.sub_id
order by t1.base_id,t1.lot_id,t1.split_id,t1.sub_id,t2.opr

[tt]

which gives these results...

Type Base lot split sub opr opr_rsc_id prevop nextopr
W AG03686-014 SH1 0 0 12 INSPMAT -1 397
W AG03686-014 SH1 0 0 397 PMMANUBLAST 12 402
W AG03686-014 SH1 0 0 402 FIN-007 397 412
W AG03686-014 SH1 0 0 412 PMPRESV 402 422
W AG03686-014 SH1 0 0 422 PMPACK 412 9999
W AG03686-014 SH1 0 0 9999 FIN-007 422 -1[/tt]

I want to add another table Shop_Resource to the mix to get a couple more fields, resource_1,resource_2.
The Shop_Resource table is joined to the operation table by resource_id. I want to get that info for Opr,nextopr and prevopr. So I want to add columns
OprResource1,OprResource2(I already have resource_id)
prevResource_id,PrevOpResource1,PrevOpResource2, NextResourceId,NextOpResource1, NextOpResource@.

I'm wondering if the

SELECT MAX(sequence_no)
     FROM operation
    WHERE workorder_type = 'W'  AND
    workorder_base_id = op1.workorder_base_id AND workorder_lot_id =
     op1.workorder_lot_id AND workorder_Split_id = op1.workorder_split_id
      AND Workorder_sub_id = op1.workorder_sub_id AND sequence_no <
      op1.sequence_no), - 1) AS PrevOp

could be altered to include to resource_id field that's in the Operation table for that Operation. Likewise for nextop.

Or should I just join the results of the 1st query to the Operation table 2 times, once for prev op and once for next op and then join to the resource table. I'm not good at understanding what gets done 1st.
My operation table contains 364058 records.
When I run my I'm selecting only 29843 records and the query takes a second.