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: 2017-11-06

Who is Participating?

Solutions

LVL 38

Pawan KumarConnect With a Mentor Database ExpertCommented: 2017-11-06

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

LVL 38

Pawan KumarDatabase ExpertCommented: 2017-11-06

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.

LVL 5

JohnAuthor Commented: 2017-11-06

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

LVL 38

Pawan KumarDatabase ExpertCommented: 2017-11-06

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

LVL 5

JohnAuthor Commented: 2017-11-06

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