Query Problem
Query Problem
(OP)
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.
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.
CODE --> sql
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%') 
Talk To Other Members
RE: Query Problem
CODE
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.
RE: Query Problem
Simi
RE: Query Problem
CODE --> sql
RE: Query Problem
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.
RE: Query Problem
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.
CODE --> sql
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)RE: Query Problem
Simi
RE: Query Problem
CODE
SELECT P.ProductID , P.ProductCode , SUM(St.StockActual) As SumStockActual , SUM(PP.TotalVolume) As SumTotalVolume , 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) GROUP BY P.ProductID , P.ProductCode , PP.PackStatus , PP.DeletedRE: Query Problem