Avatar of John
John
Flag for United Kingdom of Great Britain and Northern Ireland asked on

MS SQL JOIN problem

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

Open in new window


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

Open in new window


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
DatabasesSQL* joins

Avatar of undefined
Last Comment
John

8/22/2022 - Mon
Pawan Kumar

Please try this

You need to put Where clause before Group by.

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
where StockCode is null
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

Open in new window


Also note that we can use alias name to simplify the column names.
John

ASKER
The second code snippet was incorrect, I copied the wrong one.  

This is the correct one and it doesn't work as per the errors above

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 RIGHT OUTER 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
WHERE dbo.StockItems.StockCode is null

Open in new window

Pawan Kumar

Please try this-

Changed code.

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 RIGHT OUTER 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
WHERE dbo.StockItems.StockCode is null
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

Open in new window

Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
ASKER CERTIFIED SOLUTION
Pawan Kumar

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
John

ASKER
Thanks Pawan, you ansered so quickly I didn't see it


This runs and brings back records:
ECT        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 RIGHT OUTER 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
WHERE        (dbo.StockItems.StockCode IS NULL)
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

Open in new window


Thanks for the speedy fix
Pawan Kumar

Welcome. Glad to help.:)
John

ASKER
Thanks also for the tip on aliasing, it makes it a ton easier to read!
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.