Help with SQL Statements.

[Redsz]

I have been working with two SQL statements for the last little bit and am unable to determine why they are not matching.

These statments were created with SQL servers query analyzer before being placed into production code.

SELECT ITEMS.Item_Name as 'ItemName', ITEMS.Item_ID as 'ItemID', 
SUM(PSData.quantity) as 'Qty'
FROM PSData 
INNER JOIN STORE_RECIPE_PLUs ON PSData.plu = STORE_RECIPE_PLUs.PLU   
INNER JOIN STORE_RECIPES ON STORE_RECIPE_PLUs.Recipe_ID = STORE_RECIPES.Recipe_ID   
INNER JOIN RECIPE_ITEMS ON STORE_RECIPES.Recipe_ID = RECIPE_ITEMS.Recipe_ID   
INNER JOIN ITEMS ON ITEMS.Item_ID = RECIPE_ITEMS.Item_ID   
INNER JOIN STORE_ITEMS ON STORE_ITEMS.Item_ID = ITEMS.Item_ID AND STORE_ITEMS.Store_ID = PSData.StoreID    
WHERE (PSData.datepolled='5/12/2004' OR PSData.datepolled='5/5/2004')
AND PSData.storeid IN ('WEN001') 
AND STORE_RECIPE_PLUs.Store_ID IN ('WEN001') 
AND STORE_RECIPES.Store_ID IN ('WEN001') 
AND STORE_RECIPES.Active=1 
AND STORE_ITEMS.Active=1  
AND RECIPE_ITEMS.Item_ID = 449
GROUP BY ITEMS.Item_ID, ITEMS.Item_Name
ORDER BY ITEMS.Item_ID

this returns a sum quantity of 159

SELECT 
sum(quantity) AS 'Amount Sold'
FROM PSDATA WHERE Storeid='WEN001' AND (datepolled='5/12/2004' or datepolled='5/5/2004')
and plu IN
(
 SELECT PLU FROM STORE_RECIPE_PLUs 
 WHERE STORE_RECIPE_PLUs.Store_ID='WEN001'
 AND STORE_RECIPE_PLUs.Recipe_ID IN 
 (
  SELECT STORE_RECIPES.RECIPE_ID 
  FROM STORE_RECIPES 
  INNER JOIN RECIPE_ITEMS ON STORE_RECIPES.Recipe_ID=RECIPE_ITEMS.Recipe_ID
  WHERE RECIPE_ITEMS.Item_id=449
 )
)

this returns a quantity of 81

Does anybody see anything that is different with the above statemnt? Or anything that i am doing wrong? The amount of 81 is correct by the way.

 

[Redsz]

Removing even more code from the first statement and it still procudes the result of 159.

SELECT RECIPE_ITEMS.Item_ID as 'ItemID', 
SUM(PSData.quantity) as 'Qty'
FROM PSData 
INNER JOIN STORE_RECIPE_PLUs ON PSData.plu = STORE_RECIPE_PLUs.PLU   
INNER JOIN STORE_RECIPES ON STORE_RECIPE_PLUs.Recipe_ID = STORE_RECIPES.Recipe_ID   
INNER JOIN RECIPE_ITEMS ON STORE_RECIPES.Recipe_ID = RECIPE_ITEMS.Recipe_ID   
WHERE (PSData.datepolled='5/12/2004' OR PSData.datepolled='5/5/2004')
AND PSData.storeid IN ('WEN001') 
AND STORE_RECIPE_PLUs.Store_ID IN ('WEN001') 
AND STORE_RECIPES.Store_ID IN ('WEN001') 
AND RECIPE_ITEMS.Item_ID = 449
GROUP BY RECIPE_ITEMS.Item_ID
ORDER BY RECIPE_ITEMS.Item_ID

Your help is appreciated

 

[zhavic]

I think it's too late,
but I found one difference - in your first post, the second statement has no this condition:

AND STORE_RECIPES.Store_ID IN ('WEN001')

Zhavic

---------------------------------------------------------------
In the 1960s you needed the power of two Comodore64s to get a rocket to the moon. Now you need a machine which is a vast number of times more powerful just to run the most popular GUI.