I bet this is dead easy for someone who knows SQL.
I am trying to pull back some data from a couple of databases. I'm not fluent with joins yet, so I create a view to get what I want, then copy the SQL code to use it in a SP.
I have created the following View in the StockTake database:
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 SystemQuantity, SUM(dbo.StockEntries.SlipQuantity) AS Total, SUM(dbo.StockEntries.SlipQuantity) - SysproCompanyD.dbo.InvWarehouse.QtyOnHand AS Variance, CAST(SUM(dbo.StockEntries.SlipQuantity - SysproCompanyD.dbo.InvWarehouse.QtyOnHand) * SysproCompanyD.dbo.InvWarehouse.UnitCost AS numeric(18, 2)) AS VarianceValueFROM dbo.StockItems INNER JOIN dbo.StockEntries ON dbo.StockItems.StockCode = dbo.StockEntries.StockCode INNER JOIN SysproCompanyD.dbo.InvMaster ON dbo.StockItems.StockCode = SysproCompanyD.dbo.InvMaster.StockCode INNER 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.ID
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.