tsql : how do I join this table?

Question

I have the following sql statement:

SELECT TOP (100) PERCENT inv.CompanyID, 
                         cust.CustID AS ClientID, 
                         cust.CustName AS CustomerName, 
                         inv.InvcKey, inv.PrimarySperKey AS SperKey, 
                         inv.TranID AS InvoiceNumber, 
                         inv.TranDate AS PostDate, 
                         sper.SperName AS SalesPersonName, 
                         inv.SalesAmt AS InvoiceSubAmount, 
                         inv.TranAmt AS InvoiceTotal, 
                         detl.ItemKey, 
                         detl.InvoiceLineKey AS dtInvoiceLineKey, 
                         detl.Description, 
                         detl.UnitCost AS calcUnitCost, 
                         detl.UnitPrice, 
                         detl.ExtAmt, 
                         (detl.UnitPrice - detl.UnitCost) * dist.QtyShipped - detl.TradeDiscAmt AS detLineGrossProfit, 
                         dbo.tPA00175.chrJobNumber AS ARJobNumber, 
                         dist.QtyShipped, 
                         dbo.timItem.ItemID AS ARItemID, 
                         dbo.timItemClass.ItemClassID AS ARItemClass,         
                         dist.TradeDiscAmt,                       
                         dbo._v_GP_SalesTerr.SalesTerritoryID

FROM         
                      dbo.tarInvoiceDetl AS detl RIGHT OUTER JOIN
                      dbo.timItem INNER JOIN
                      dbo.timItemClass ON dbo.timItem.ItemClassKey = dbo.timItemClass.ItemClassKey ON detl.ItemKey = dbo.timItem.ItemKey RIGHT OUTER JOIN
                      dbo._v_GP_SalesTerr RIGHT OUTER JOIN
                      dbo.tarInvoice AS inv INNER JOIN
                      dbo.tarCustomer AS cust ON inv.CustKey = cust.CustKey ON dbo._v_GP_SalesTerr.CustKey = cust.CustKey ON 
                      detl.InvcKey = inv.InvcKey LEFT OUTER JOIN
                      dbo.tPA00175 INNER JOIN
                      dbo.paarInvcHdr ON dbo.tPA00175.intJobKey = dbo.paarInvcHdr.intJobKey ON inv.InvcKey = dbo.paarInvcHdr.InvcKey LEFT OUTER JOIN
                      dbo.tarInvoiceLineDist AS dist ON detl.InvoiceLineKey = dist.InvoiceLineKey LEFT OUTER JOIN
                      dbo.tarSalesperson AS sper ON inv.PrimarySperKey = sper.SperKey

If I wanted to add a field called Inventory.LastUnitCost where Inventory had a surrogate key called itemkey for the table "timitem", how would i add this in there where it would just look up the LastUnitCost field by itemkey? If there wasn't a LastUnitCost corresponding to the timitem table, it would just be null..

Everytime I attempt to add it as in Inner Join to timItem on Inventory.itemkey = timItem.itemkey, it affects the timItemClass.ItemClassID field where some of the catorgies do not show up anymore.

thanks in advance.

Answer 1

Add Inventory.LastUnitCost to the SELECT list

and add this line to the end of the query:

dbo.Inventory LEFT OUTER JOIN dbo.timItem ON dbo.timItem.itemkey = dbo.Inventory.itemkey