John

asked on 11/6/2017

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

Last Comment

John

8/22/2022 - Mon

Pawan Kumar

11/6/2017

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

11/6/2017

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

11/6/2017

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

11/6/2017

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

11/6/2017

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

11/6/2017

Welcome. Glad to help.:)

John

11/6/2017

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.