We help IT Professionals succeed at work.

SQL Statement assistance with AND NOT query splitting out

142 Views
Last Modified: 2017-02-19
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!
Comment
Watch Question

CERTIFIED EXPERT
Top Expert 2010
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Norm-alNetwork Engineer

Author

Commented:
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
CERTIFIED EXPERT
Top Expert 2010

Commented:
I don't understand.  Why isn't the query running exactly as you type it?
CERTIFIED EXPERT
Top Expert 2010

Commented:
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.
Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
PortletPaulEE Topic Advisor
CERTIFIED EXPERT
Most Valuable Expert 2014
Awarded 2013

Commented:
They are both broken out when executed
by what?

Please identify how you are using the sql
Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
@Author - Have you tried the code I have given above. ?
G Trurab KhanSnr. Development Manager
CERTIFIED EXPERT

Commented:
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'))
Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
@Author - Have you tried above suggestions ?
Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
@Author - Any update?
Vitor MontalvãoIT Engineer
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
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?
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 KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
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ãoIT Engineer
CERTIFIED EXPERT
Distinguished Expert 2017

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

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ãoIT Engineer
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
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.
CERTIFIED EXPERT
Top Expert 2010

Commented:
Victor is right.  And the queries I posted earlier use the correct logic.
Vitor MontalvãoIT Engineer
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
Bianca, can you give us a feedback if the issue has been solved?
Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
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.
Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
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-alNetwork Engineer

Author

Commented:
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.
CERTIFIED EXPERT
Top Expert 2010

Commented:
Bianca - please let us know which answer(s) resolved your question.
Vitor MontalvãoIT Engineer
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
Bianca, what do you want to do with this question?
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.