I am using vb6 and sql server 2000.

I need join 3 tables Stops, Trans, Comms.
Stops
Left join Trans t on stops.stop_num = trans.stop_num and stops.stopdate = trans.trandate
and stops.route_num = trans.route_num
Where itemid <> "NOPU"

left join Comms on comms.commid = trans.commodity

Where t.TranType = "P"
SUM(Qty) and Sum(Amount) where commodity = C as Tot Cash Pieces PU and Tot Cash Amount PU
SUM(Qty) and Sum(Amount) where comms.code = "BA" or "BO" as Tot Coin Pieces PU and Tot Coin Amount PU

Where t.TranType = "D"
SUM(Qty) and Sum(Amount) where commodity = C as Tot Cash Pieces Del and Tot Cash Amount Del
SUM(Qty) and Sum(Amount) where comms.code = "BA" or "BO" as Tot Coin Pieces Del and Tot Coin Amount Del

I know I need left join Trans and then Trans left join Comms:
I have code like this:
Code:
  .......
  strSQL = strSQL & "FROM Stops s  "
  strSQL = strSQL & "Left Join Trans t ON (s.Stop_Num = t.stop_num "
  strSQL = strSQL & "AND s.StopDate = t.TranDate "
  strSQL = strSQL & "AND s.Route_Num = t.Route_Num) "
  ????
How do I write the 2nd left Join?
Thanks a lot.