Avatar of Norm-al
Norm-al
Flag for United States of America asked on

SQL Statement assistance with AND NOT query splitting out

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

I want to pull all parts except for those that are (piecetype = 65 and material=melamine) AND (piecetype = 65 and material=prefinished) there will be parts with piecetype 65 with different material i want to pull.. or there may be parts with different piecetype but same material that i want to pull as well. i only want to remove those 2 materials for that piece type.

Thanks!
Microsoft SQL ServerSQL

Avatar of undefined
Last Comment
Vitor Montalvão

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
zephyr_hex (Megan)

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Norm-al

ASKER
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
zephyr_hex (Megan)

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

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.
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
SOLUTION
Pawan Kumar

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
PortletPaul

They are both broken out when executed
by what?

Please identify how you are using the sql
Pawan Kumar

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

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'))
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Pawan Kumar

@Author - Have you tried above suggestions ?
Pawan Kumar

@Author - Any update?
Vitor Montalvão

This one should work (already posted by zephyr_hex in his first comment):
WHERE NOT (PieceType = 65 AND material IN ('melamine','prefinished'))

Open in new window

Can you post full query and some sample data so we can realize better what's going here?
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
Member_2_276102

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)".
Pawan Kumar

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)
Vitor Montalvão

Why he needs two selects for a simple logical operation?
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Member_2_276102

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

Open in new window

Probably should be rewritten by moving some parenthses:
WHERE     NOT ( PieceType = 65  AND
               (material = 'melamine' OR material = 'prefinished'))

Open in new window

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

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.
zephyr_hex (Megan)

Victor is right.  And the queries I posted earlier use the correct logic.
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
Vitor Montalvão

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

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

Regards,
Pawan
Member_2_276102

@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.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Pawan Kumar

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) 

Open 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 )

Open in new window


Regards,
Pawan
Norm-al

ASKER
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.
zephyr_hex (Megan)

Bianca - please let us know which answer(s) resolved your question.
Your help has saved me hundreds of hours of internet surfing.
fblack61
Vitor Montalvão

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