×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Query Problem
3

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.

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%') 

RE: Query Problem

I would try:

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

Is .ProductPack.PackStatus Char or Numeric?

Simi

RE: Query Problem

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

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 
	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%') 

RE: Query Problem

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.



RE: Query Problem

(OP)
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.


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

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

Simi

RE: Query Problem

How about:

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.Deleted 

RE: Query Problem

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close