CODEWITHSUNDEEP
sqlsql-servert-sqlsql-view
SeekWhat
SeekWhat

Reputation: 45

Do I Need a Subquery on SQL View?

I created the first part of the query which shows the total quantity of a product currently waiting to be sent out to customers & returns me the item number from the Item table - 

SELECT
  [Item].[ItemNum],
  SUM([Sales].[Quantity]) AS [Qty]
FROM [dbo].[Item]
LEFT OUTER JOIN [dbo].[Sales]
  ON [Item].[ItemNum] = [Sales].[ItemNum]
WHERE ([Sales].[Sale Type] = 1)
GROUP BY [Item].[ItemNum]

That works great, I get the catalogue number of the item and how many are on order. Now I need to join to another table, which shows me stock of items. This is what the query looks like - 

SELECT
  [Item].[ItemNum] SUM ([Sales].[Quantity]) AS [Qty], SUM ([Stock].[Remaining Quantity])
AS [Free Stock]
FROM [dbo].[Item]
LEFT OUTER JOIN dbo.[Sales]
  ON [Item].[ItemNu]m = [Sales].[ItemNum]
LEFT OUTER JOIN [dbo].[Stock]
  ON [Item].[ItemNum] = [Stock].[ItemNum]
WHERE ([Sales].[Document Type] = 1)
GROUP BY [Item].[ItemNum]

Now the quantity on order is way out and the quantity in stock is wrong as well. As I have the 'where' applied to the sales table but it doesn't apply to the stock table, do I need to move it out?

Thanks

Upvotes: 1

Views: 58

Answers (2)

Meyssam Toluie
Meyssam Toluie

Reputation: 1071

In this case Common Table Expression is useful:

With CTE As (
SELECT
  [Item].[ItemNum],
  SUM([Sales].[Quantity]) AS [Qty]
FROM [dbo].[Item]
LEFT OUTER JOIN [dbo].[Sales]
  ON [Item].[ItemNum] = [Sales].[ItemNum]
WHERE ([Sales].[Sale Type] = 1)
GROUP BY [Item].[ItemNum]
 )
  Select *
    From CTE Left Outer Join
     [dbo].[Stock]
  ON [CTE].[ItemNum] = [Stock].[ItemNum]

Upvotes: 0

HLGEM
HLGEM

Reputation: 96552

I don't know if it will fix your problem but the where clause is turning your left join into an inner join. Try this instead.

SELECT
  dbo.[databasename$Item].ItemNum, SUM (dbo.[databasename$Sales].Quantity) AS [Qty], SUM (dbo.[databasename$Stock.[Remaining Quantity])
AS [Free Stock]
FROM dbo.[databasename$Item]
LEFT OUTER JOIN dbo.[databasename$Sales]
  ON dbo.[databasename$Item].ItemNum = dbo.[databasename$Sales].ItemNum
  AND (dbo.[databasename$Sales].[Document Type] = 1)
LEFT OUTER JOIN dbo.[databasename$Stock]
  ON dbo.[databasename$Item].ItemNum = dbo.[databasename$Stock].[ItemNum]
GROUP BY dbo.[databasename$Item].ItemNum

Try this and see if it fixes the issue:

SELECT
  dbo.[databasename$Item].ItemNum, [databasename$Sales].Quantity) AS [Qty], [databasename$Stock.[Remaining Quantity])
AS [Free Stock]
FROM dbo.[databasename$Item]
LEFT OUTER JOIN (select ItemNum, sum(quantity) as Quantity from dbo.[databasename$Sales] where Document Type = 1group by ItemNum) as databasename$Sales
  ON dbo.[databasename$Item].ItemNum = [databasename$Sales].ItemNum
LEFT OUTER JOIN (select ItemNum, sum(quantity)  as RemainingQuantity from dbo.[databasename$Stock]roup by ItemNum)
as databasename$Stock
  ON dbo.[databasename$Item].ItemNum = [databasename$Stock].[ItemNum]
GROUP BY dbo.[databasename$Item].ItemNum

Upvotes: 1

Related Questions