troubleshooting Question

MS SQL JOIN problem

Avatar of John
JohnFlag for United Kingdom of Great Britain and Northern Ireland asked on
DatabasesSQL* joins
7 Comments1 Solution66 ViewsLast Modified:
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 VarianceValue
FROM            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.StockCode
GROUP 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 SystemQuantity
FROM            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.StockCode
GROUP 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
where StockCode is null

I get lots of rows when I don't have the Where clause.  When I include the where clause, I get the error below.  

Error in GROUP BY clause.
Unable to parse query text.

I ok that and up pops a message saying
Incorrect syntax near the keyword WHERE

I get this error and I'm stumped.  I tried adding a Group By clause and it made no difference.  

To be brief in case I waffled a bit, I want all the records from the syspro database where the stockcode is not included in the stockTake database.

Thanks in advance

John
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 7 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 7 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros