问将字段从另一个表中添加到exising查询中

EN

SELECT 
   CAST(TB2.QTY_UNALLOCATED as nvarchar) [WH Free Stock], TB2.PRODUCT, ISNULL(NewLive.ProductGroup,'NoProductGroupFound')
FROM 
 (SELECT T40.PRODUCT , SUM(T40.QUANTITY * T40.PACKSIZE) - SUM(ISNULL(T41.QTY_PICKLOC, 0)) - SUM(ISNULL(T42.QTY_REPLENIS, 0)) QTY_UNALLOCATED
                              FROM A1WMS.DBO.BINLOCAT T40 
                                    JOIN A1WMS.DBO.BINMSTR T47 ON T40.BINLABEL = T47.BINLABEL
                                    LEFT JOIN (SELECT T41.EXTENDED, T41.BINLABEL, T41.PACKSIZE, SUM((T41.QTY_TOPICK - T41.QTY_PICKED) / T41.PACKSIZE) QTY_PICKLOC
                                                      FROM A1WMS.DBO.PICKLOC T41 WHERE T41.PCKLOC_ST = '' AND T41.ALLOCATED = '1'
                                                      GROUP BY T41.EXTENDED, T41.BINLABEL, T41.PACKSIZE) T41 ON T40.EXTENDED = T41.EXTENDED AND T40.BINLABEL = T41.BINLABEL AND T40.PACKSIZE = T41.PACKSIZE
                                    LEFT JOIN (SELECT T42.EXTENDED, T42.BULKSLOT, T42.BULKPACK, SUM((T42.QTY_REQD * T42.PACKSIZE) / T42.BULKPACK) QTY_REPLENIS
                                                      FROM A1WMS.DBO.REPLENIS T42
                                                      GROUP BY T42.EXTENDED, T42.BULKSLOT, T42.BULKPACK) T42 ON T40.EXTENDED = T42.EXTENDED AND T40.BINLABEL = T42.BULKSLOT AND T40.PACKSIZE = T42.BULKPACK
                              WHERE (T40.QUANTITY > 0 OR T40.BINTYPE <> 'S') AND T40.RESERVED NOT IN ('B', 'I') AND
                              T40.BINLABEL NOT LIKE '#%' AND T47.RESERVED <> 'B' AND T40.BINLABEL NOT LIKE 'RECV%' AND
                              T40.BINLABEL NOT LIKE 'QUAR%' AND T40.BINLABEL NOT LIKE 'REP%' AND T40.BINLABEL NOT LIKE 'RET%' AND
                              T40.ZONE NOT IN ('Q','S','L','C','R')
                              GROUP BY T40.PRODUCT) TB2
LEFT JOIN NewLive.dbo.OITM NewLive on NewLive.Product = TB2.Product