Query Problem 3

[Cpreston]

Hi

I have a query that although I am saying <> 13 it is bringing in results for 13. I have tried different joins, but cannot get it to take out rows with <> 13 on the status filed. Attached is the code and also a screen shot of the result. Any ideas please.

SELECT     dbo.Product.ProductID, dbo.Product.ProductCode, dbo.Stock.StockActual, dbo.ProductPack.TotalVolume, dbo.ProductPack.PackStatus, dbo.ProductPack.Deleted
FROM         dbo.ProductPack INNER JOIN
                      dbo.Stock INNER JOIN
                      dbo.Product ON dbo.Stock.ProductID = dbo.Product.ProductID ON dbo.ProductPack.ProductID = dbo.Stock.ProductID
WHERE     (dbo.Product.ProductCode LIKE 'ZZ%') AND (dbo.ProductPack.PackStatus <> 13) OR
                      (dbo.Product.ProductCode LIKE 'X%')

 

[Andrzejek]

I would try:

WHERE (dbo.Product.ProductCode LIKE 'ZZ%' OR dbo.Product.ProductCode LIKE 'X%') 
    AND (dbo.ProductPack.PackStatus <> 13)

---- Andy

There is a great need for a sarcasm font.

 

[simian336]

Is .ProductPack.PackStatus Char or Numeric?

Simi

 

[simian336]

Off topic, but a few little style changes would make your code so much easier to read....

SELECT dbo.Product.ProductID, dbo.Product.ProductCode
, dbo.Stock.StockActual, dbo.ProductPack.TotalVolume
, dbo.ProductPack.PackStatus, dbo.ProductPack.Deleted
FROM dbo.ProductPack 
INNER JOIN dbo.Stock 
	ON dbo.ProductPack.ProductID = dbo.Stock.ProductID
INNER JOIN dbo.Product 
	ON dbo.Stock.ProductID = dbo.Product.ProductID
WHERE (dbo.Product.ProductCode LIKE 'ZZ%') 
AND (dbo.ProductPack.PackStatus <> 13) 
OR (dbo.Product.ProductCode LIKE 'X%')

 

[Olaf Doschke]

Boolean Algebra is not on your side, Cpreston.

A AND B OR C is true, even when only C is true. So all X% are listed.

The way you put your major condition always needing to be true as B, you also make it impossible to put a bracket on the conditions (A OR C).
Put any condition you always want true at the begin and combine them with AND. Bundle conditions that are optional or additive at the end and OR them, put all this into a bracket and combine that with al the first conditions with AND again, that covers many many cases, though not all.

Always remember the conditions you ask for are checked in each row for that single row. If you look for products of both the codes ZZ% AND X% you still need the OR operator, not AND. Why? Because any single product has either a code starting with ZZ or X, not both at the same time. The "both" applies to the whole list, but not a single product. You always formulate your filter condition for every single product.

And last not least, every time you have an OR without any bracketing with one or only a few other conditions, that OR is on the top level, then the single condition is sufficient for a row to become part of the result, all other conditions are unimportant, so a single ORed condition always should ring your alarms.

Bye, Olaf.

 

[Cpreston]

Hi

Thanks all for the great reply's. I now have the query as below and it takes out the 13 status. I now would like to SUM the StockActual and the TotalVolume, would this be possible in the way ther query is done. Many thanks, by the way the packstatus field is tinyinbt.

SELECT     dbo.Product.ProductID, dbo.Product.ProductCode, dbo.Stock.StockActual, dbo.ProductPack.TotalVolume, dbo.ProductPack.PackStatus, dbo.ProductPack.Deleted
FROM         dbo.ProductPack INNER JOIN
                      dbo.Stock INNER JOIN
                      dbo.Product ON dbo.Stock.ProductID = dbo.Product.ProductID ON dbo.ProductPack.ProductID = dbo.Stock.ProductID
WHERE (dbo.Product.ProductCode LIKE 'ZZ%' OR dbo.Product.ProductCode LIKE 'X%') 
    AND (dbo.ProductPack.PackStatus <> 13)

 

[simian336]

You want a sum of all of them or by dbo.Product.ProductID or dbo.Product.ProductCode?

Simi

 

[Andrzejek]

How about:

SELECT P.ProductID
     , P.ProductCode[blue]
     , SUM(St.StockActual) As SumStockActual
     , SUM(PP.TotalVolume) As SumTotalVolume[/blue]
     , PP.PackStatus
     , PP.Deleted
FROM dbo.ProductPack PP
INNER JOIN dbo.Stock St INNER JOIN dbo.Product P
        ON St.ProductID = P.ProductID 
        ON PP.ProductID = St.ProductID
WHERE (P.ProductCode LIKE 'ZZ%' 
    OR P.ProductCode LIKE 'X%') 
  AND (PP.PackStatus <> 13) [blue]
GROUP BY P.ProductID
     , P.ProductCode
     , PP.PackStatus
     , PP.Deleted[/blue]

 

[Cpreston]

That's great thanks everyone, think I have it now.