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
LVL 5
JohnAsked:
Who is Participating?
 
Pawan KumarConnect With a Mentor Database ExpertCommented:
With proper aliasing, you can use below code :)

SELECT si.ID, si.Status, si.Warehouse, si.StockCode, Im.Description AS StockDesc, Iw.UnitCost,Iw.QtyOnHand AS SystemQuantity
FROM   dbo.StockItems si 
	RIGHT OUTER JOIN SysproCompanyD.dbo.InvMaster Im ON si.StockCode = Im.StockCode 
	INNER JOIN SysproCompanyD.dbo.InvWarehouse Iw ON Im.StockCode = Iw.StockCode
where si.StockCode is null
GROUP BY si.Warehouse, si.Status, si.StockCode, Im.InvMaster.Description, Iw.UnitCost,Iw.QtyOnHand, si.ID

Open in new window

0
 
Pawan KumarDatabase ExpertCommented:
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.
0
 
JohnAuthor Commented:
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

0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
Pawan KumarDatabase ExpertCommented:
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

0
 
JohnAuthor Commented:
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
0
 
Pawan KumarDatabase ExpertCommented:
Welcome. Glad to help.:)
0
 
JohnAuthor Commented:
Thanks also for the tip on aliasing, it makes it a ton easier to read!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.