Joining Additional Table Column

Question

I need to add another column from another table i.e. CusSorMaster+.SalesOrder.The join is on where dbo.SorMaster.SalesOrder = dbo.CusSorMaster+.SalesOrder. This wasn't written by me and I need to manipulate it to include the other column.When I join the table with the column I don't get the same rows as my original query which I want. Please assist and feel free to ask any questions.

SELECT RIGHT(dbo.SorMaster.SalesOrder,6) AS SalesOrder,
       dbo.SorMaster.CustomerPoNumber,
       dbo.SorDetail.SalesOrderLine,
       dbo.SorDetail.MStockCode,
       dbo.SorDetail.MStockDes,
       dbo.SorDetail.MOrderQty,
       dbo.SorDetail.MCusSupStkCode,
       dbo.SorDetail.LineType,
       dbo.SorMaster.OrderStatus,
       dbo.[CusSorDetailMerch+].DuparJobNumber,
       dbo.SorMaster.Customer,
       dbo.SorDetail.MShipQty,
       dbo.SorDetail.MBackOrderQty,
       CASE
           WHEN
       RIGHT(dbo.SorMaster.SalesOrder,6) = '211125'
           THEN dbo.SorDetail.MOrderQty
           ELSE dbo.SorDetail.MShipQty + dbo.SorDetail.MBackOrderQty
       END AS ShipPlusBackorderQty,
       dbo.InvAltStock.AltStockCode,
       RIGHT(STUFF(DATEPART(year,GETDATE()),1,0,'0'),2) + RIGHT(STUFF(DATEPART(week,GETDATE()),1,0,'0'),2) AS YearWeekNo,
       'B00ABD' + RIGHT(STUFF(DATEPART(year,GETDATE()),1,0,'0'),2) + RIGHT(STUFF(DATEPART(week,GETDATE()),1,0,'0'),2) + '' AS SerialPrefix,
       dbo.[CusSorDetailMerch+].InvoiceNumber
  FROM dbo.SorMaster WITH(NOLOCK)
 INNER JOIN dbo.SorDetail WITH(NOLOCK) ON dbo.SorMaster.SalesOrder = dbo.SorDetail.SalesOrder 
 LEFT OUTER JOIN dbo.InvAltStock ON dbo.SorDetail.MStockCode = dbo.InvAltStock.StockCode 
 LEFT OUTER JOIN dbo.[CusSorDetailMerch+] ON dbo.SorDetail.SalesOrder = dbo.[CusSorDetailMerch+].SalesOrder
                                                      AND dbo.SorDetail.SalesOrderLine = dbo.[CusSorDetailMerch+].SalesOrderInitLine
 WHERE(
       dbo.SorDetail.LineType = '1'
       OR
       dbo.SorDetail.LineType = '7')
      AND (
       dbo.SorMaster.OrderStatus <> '9')
      AND (
       dbo.SorMaster.OrderStatus <> '\')
      AND (
       dbo.SorMaster.OrderStatus <> '*')
      AND (
       dbo.[CusSorDetailMerch+].InvoiceNumber = ''
       OR
       dbo.[CusSorDetailMerch+].InvoiceNumber = '000000000000000'
       OR dbo.[CusSorDetailMerch+].InvoiceNumber IS NULL);

Answer 1

In order to preserve the "left" part of the join (= your current rows), you need to join with a left outer join. You can learn about outer joins in the documentation.

By the way, if you only need the field you are joining on, then joining is not required...

I took the liberty of:

• reformatting the query,
• rewriting parts of the WHERE clause to use the IN keyword,
• removing the keyword outer (it is optional in T-SQL),
• removing the WITH (nolock) options, put them back if you really need them.

Updated query:

SELECT  RIGHT(dbo.SorMaster.SalesOrder, 6) AS 'SalesOrder',
        dbo.SorMaster.CustomerPoNumber,
        dbo.SorDetail.SalesOrderLine,
        dbo.SorDetail.MStockCode,
        dbo.SorDetail.MStockDes,
        dbo.SorDetail.MOrderQty,
        dbo.SorDetail.MCusSupStkCode,
        dbo.SorDetail.LineType,
        dbo.SorMaster.OrderStatus,
        dbo.[CusSorDetailMerch+].DuparJobNumber,
        dbo.SorMaster.Customer,
        dbo.SorDetail.MShipQty,
        dbo.SorDetail.MBackOrderQty,
        CASE
            WHEN RIGHT(dbo.SorMaster.SalesOrder, 6)= '211125'
            THEN dbo.SorDetail.MOrderQty
            ELSE dbo.SorDetail.MShipQty + dbo.SorDetail.MBackOrderQty
        END AS 'ShipPlusBackorderQty',
        dbo.InvAltStock.AltStockCode,
        RIGHT(STUFF(DATEPART(year, GETDATE()), 1, 0, '0'), 2) + RIGHT(STUFF(DATEPART(week, GETDATE()), 1, 0, '0'), 2) AS 'YearWeekNo',
        'B00ABD' + RIGHT(STUFF(DATEPART(year, GETDATE()), 1, 0, '0'), 2) + RIGHT(STUFF(DATEPART(week, GETDATE()), 1, 0, '0'), 2) + '' AS 'SerialPrefix',
        dbo.[CusSorDetailMerch+].InvoiceNumber,
        dbo.SorMaster.SalesOrder,       -- new selected field (no join required)
        dbo.[CusSorMaster+].SalesOrder, -- new selected field
        dbo.[CusSorMaster+].*           -- new selected fields: all fields from [CusSorMaster+]
FROM dbo.SorMaster
JOIN dbo.SorDetail
    ON dbo.SorMaster.SalesOrder = dbo.SorDetail.SalesOrder
LEFT JOIN dbo.InvAltStock
    ON dbo.SorDetail.MStockCode = dbo.InvAltStock.StockCode
LEFT JOIN dbo.[CusSorDetailMerch+]
    ON  dbo.SorDetail.SalesOrder = dbo.[CusSorDetailMerch+].SalesOrder
    AND dbo.SorDetail.SalesOrderLine = dbo.[CusSorDetailMerch+].SalesOrderInitLine
LEFT JOIN dbo.[CusSorMaster+]                                               -- new outer join
    ON dbo.SorMaster.SalesOrder = dbo.[CusSorMaster+].SalesOrder
WHERE dbo.SorDetail.LineType in ('1', '7')                                  -- rewrote OR
  AND dbo.SorMaster.OrderStatus not in ('9', '\', '*')                      -- rewrote OR
  AND (dbo.[CusSorDetailMerch+].InvoiceNumber in ('', '000000000000000')    -- partially rewrote OR
    OR dbo.[CusSorDetailMerch+].InvoiceNumber IS NULL);