Bill of Lading help

[viking79]

 Hello, 

I made a modified bill of lading report to fit the type of items we actually ship. We have a table called PARTTRACKING and one called TRACKINGTEXT that sort of go together. I'm trying to get those two tables into the bill of lading query, but the few things I've tried didn't work. I have pasted the code for the bill of lading that it starts with. I end up going back to this one everytime I screw something up. I really need those two tables to fill out the main detail of the BOL.

Any help is greatly appreciated.

Code:

SELECT ship.ordertypeid AS ordertype, ship.orderid AS orderid, ship.billoflading AS bolnumber,    ship.note AS shipnote, ordertype.name AS ordertypename, ship.cartoncount, ship.locationgroupid AS locationgroupid,    carrier.name AS carriername, carrier.scac AS carrierscac,    COALESCE(so.shiptoname, po.remittoname, xo.shiptoname) AS shipname,    COALESCE(so.shiptoaddress, po.remitaddress, xo.shiptoaddress) AS shipaddress,    COALESCE(so.shiptocity, po.remitcity, xo.shiptocity) AS shipcity,    stateconst.code AS shipstate, countryconst.name AS shipcountry,    COALESCE(so.shiptozip, po.remitzip, xo.shiptozip) AS shipzip,    COALESCE(so.num, po.num, xo.num) AS ordernumber,    COALESCE(so.note, po.note, xo.note) AS ordernote,    shipterms.name AS shipterms, fobpoint.name AS fobpoint,    shipcarton.id AS cartonid, shipitem.qtyshipped, uom.code AS uomcode,    COALESCE(soitem.productnum, poitem.partnum, xoitem.partnum) AS partnum,    COALESCE(soitem.description, poitem.description, xoitem.description) AS partdescription,    COALESCE(weightTable.weight, 0) AS cartonWeight,    COALESCE(weightUom.code, 'lbs') AS weightUomCodeFROM ship    INNER JOIN shipitem ON ship.id = shipitem.shipid    INNER JOIN carrier ON ship.carrierid = carrier.id    INNER JOIN shipcarton ON (ship.id = shipcarton.shipid AND shipitem.shipcartonid = shipcarton.id)    INNER JOIN (SELECT SUM(shipcarton.freightweight) AS weight, shipcarton.shipid               FROM shipcarton	     GROUP BY shipid) AS weightTable ON weightTable.shipid = ship.id    INNER JOIN ordertype ON ship.ordertypeid = ordertype.id    INNER JOIN uom ON shipitem.uomid = uom.id    LEFT OUTER JOIN po ON (po.id = ship.orderid AND ship.ordertypeid = 10)    LEFT OUTER JOIN poitem ON (shipitem.orderitemid = poitem.id AND ship.ordertypeid = 10)    LEFT OUTER JOIN so ON (so.id = ship.orderid AND ship.ordertypeid = 20)    LEFT OUTER JOIN soitem ON (shipitem.orderitemid = soitem.id AND ship.ordertypeid = 20)    LEFT OUTER JOIN xo ON (xo.id = ship.orderid AND ship.ordertypeid = 40)    LEFT OUTER JOIN xoitem ON (shipitem.orderitemid = xoitem.id AND ship.ordertypeid = 40)    LEFT OUTER JOIN stateconst ON stateconst.id = COALESCE(so.shiptostateid, po.remitstateid, xo.shiptostateid)    LEFT OUTER JOIN countryconst ON countryconst.id = COALESCE(so.shiptocountryid, po.remitcountryid, xo.shiptocountryid)    LEFT OUTER JOIN shipterms ON shipterms.id = COALESCE(so.shiptermsid, po.shiptermsid, 0)    LEFT OUTER JOIN fobpoint ON fobpoint.id = COALESCE(so.fobpointid, po.fobpointid, 0)    LEFT OUTER JOIN sysProperties ON sysProperties.sysKey = 'Default Weight UomId'    LEFT OUTER JOIN uom weightUom ON sysProperties.sysValue = weightUom.id