It pulls entries from the stocktake application I wrote and joins it to a syspro database to pull back certain fields related to the stocktake. This all works
I have now been asked to show only the stock items in the syspro database that have NOT shown up in the stocktake. To do this, I removed the StockEntries table as it is now not needed. Then I tried changing to a right join instead of the inner join as below:
SELECT dbo.StockItems.ID, dbo.StockItems.Status, dbo.StockItems.Warehouse, dbo.StockItems.StockCode, SysproCompanyD.dbo.InvMaster.Description AS StockDesc, SysproCompanyD.dbo.InvWarehouse.UnitCost, SysproCompanyD.dbo.InvWarehouse.QtyOnHand AS SystemQuantityFROM dbo.StockItems INNER JOIN SysproCompanyD.dbo.InvMaster ON dbo.StockItems.StockCode = SysproCompanyD.dbo.InvMaster.StockCode RIGHT OUTER JOIN SysproCompanyD.dbo.InvWarehouse ON SysproCompanyD.dbo.InvMaster.StockCode = SysproCompanyD.dbo.InvWarehouse.StockCodeGROUP BY dbo.StockItems.Warehouse, dbo.StockItems.Status, dbo.StockItems.StockCode, SysproCompanyD.dbo.InvMaster.Description, SysproCompanyD.dbo.InvWarehouse.UnitCost, SysproCompanyD.dbo.InvWarehouse.QtyOnHand, dbo.StockItems.IDwhere StockCode is null
You need to put Where clause before Group by.
Open in new window
Also note that we can use alias name to simplify the column names.