SQL Statement assistance with AND NOT query splitting out

They are both broken out when executed.. first one runs like this:
WHERE     (NOT (PieceType = 65)) OR
                      (NOT (material = 'melamine')) OR
                      (NOT (PieceType = 65)) OR
                      (NOT (material = 'prefinished'))

second one:
WHERE     (NOT (PieceType = 65)) OR
                      (NOT (material IN ('melamine', 'prefinished')))

its not combining the piece type AND material requirement

I don't understand.  Why isn't the query running exactly as you type it?

You must be using some kind of query builder?  In which case, you can't control what it does with your original syntax.  My recommendation is to not use the query builder.  Or use it for your original reason, and then modify the result to correct the WHERE clause.

They are both broken out when executed

by what?

Please identify how you are using the sql

@Author - Have you tried the code I have given above. ?

Have you tried zypher suggestion # 2 which is perfect

WHERE NOT(piecetype=65 AND material IN ('melamine','prefinished'))

to cover the upper/lower case, and nulls, query may be changed to following

WHERE NOT(isnull(piecetype, 0)=65 AND lower(isnull(trim(material),'-')) IN ('melamine','prefinished'))

@Author - Have you tried above suggestions ?

@Author - Any update?

This one should work (already posted by zephyr_hex in his first comment):

WHERE NOT (PieceType = 65 AND material IN ('melamine','prefinished'))

Select allOpen in new window

Can you post full query and some sample data so we can realize better what's going here?

I want to pull all parts except for those that are (piecetype = 65 and material=melamine) AND (piecetype = 65 and material=prefinished)

There will never be any rows that match those criteria, so all rows will always be selected. Even rows that have "(piecetype = 65 and material=melamine)" will be selected. And rows that have "(piecetype = 65 and material=prefinished)" will also be selected.

Why?

Because you really want to exclude rows that are "(piecetype = 65 and material=melamine) OR (piecetype = 65 and material=prefinished)". Rows will never satisfy both of your conditions at once, but rows might satisfy one condition OR the other. You want to exclude rows that match one OR the other. A row can't be "(material=melamine)" AND "(material=prefinished)" at the same time. But some rows can be "(material=melamine)" OR "(material=prefinished)".

Agree with tliotta, that why i gave the below query but didnt get any reply

SELECT * FROM PartsTable
EXCEPT
SELECT * FROM PartsTable
WHERE (piecetype = 65) and (material=melamine OR material=prefinished)

Why he needs two selects for a simple logical operation?

Two SELECTs is a way to arrive at what the OP appears to want. It tells that database management system what is wanted. I don't know the internals of SQL Server enough to be sure, but I'd expect that it doesn't actually run both queries. Instead I'd expect the optimizer to restructure the query simply to exclude the EXCEPT rows.

Going back to the OP:

WHERE     (NOT (PieceType = 65))  AND (material = 'melamine' OR
                      material = 'prefinished'))

Select allOpen in new window

Probably should be rewritten by moving some parenthses:

WHERE     NOT ( PieceType = 65  AND
               (material = 'melamine' OR material = 'prefinished'))

Select allOpen in new window

Using EXCEPT is one way to avoid "negative" logic that can be confusing when multiple AND and OR conditions get involved.

tliotta your mind is not set to a database engine. When you have a SELECT .. FROM .. means that the engine will need to retrieve information from a table or view. So if you duplicate it then it will do it twice no matter the optimized mechanism that engine can have.

This:
WHERE NOT (PieceType = 65 AND material IN ('melamine','prefinished'))

Is equal to:
WHERE     NOT ( PieceType = 65  AND
               (material = 'melamine' OR material = 'prefinished'))

And should be the only logical to perform what the OP wants unless she didn't tell us everything.

Victor is right.  And the queries I posted earlier use the correct logic.

Bianca, can you give us a feedback if the issue has been solved?

Hi Bianca,
Any update on this question? Is it done?

Regards,
Pawan

@Vitor Montalvão,

So if you duplicate it then it will do it twice no matter the optimized mechanism that engine can have.

I don't know SQL Server internals near well enough to disagree in particular, but from V7 of DB2 UDB, it's a possibility.

In trying to track down documentary evidence, I've only found a comment by a couple DB2 IBM developers mentioning in a thread that even up to V10 of DB2 it has been undocumented behavior. Apparently, it was part of a fix in V7 that was intended to be documented in V8. Unfortunately, a secondary bug was found and while getting that fixed, the documentation part got sidetracked and forgotten.

I tend to believe that most major DBMSs keep pace with developments by competitors, so, as I said, I "expect" similar from SQL Server. But I'm fine with learning that it's not doing so.

Hi Bianca,
Have you tried code I gave earlier? or you do you have an update on this?

SELECT * FROM PartsTable
EXCEPT
SELECT * FROM PartsTable
WHERE (piecetype = 65) and (material=melamine OR material=prefinished) 

Select allOpen in new window

SELECT * FROM PartsTable r
WHERE ID NOT IN (
SELECT SomeIDColumn FROM PartsTable s
WHERE (piecetype = 65) and (material=melamine OR material=prefinished) AND r.SomeIDColumn = s.SomeIdColumn )

Select allOpen in new window

Regards,
Pawan

I was moved onto another project but will need to get back onto this one soon. I will be trying those solutions within the next week. Thank you so much and I will update as soon as I can.

Bianca - please let us know which answer(s) resolved your question.

Bianca, what do you want to do with this question?