CODEWITHSUNDEEP
sqlboolean-logic
user5438532
user5438532

Reputation:

Simple SQL AND OR Not Working

SELECT *
FROM Items
WHERE (Balance >= 0) OR (Item Like '%XF%' AND Balance > 0)

This is returning all items with a balance >= 0.

I need it to report all items with a balance >=0 OR... all items with a balance > 0 (but not equal to 0) and have 'XF' in the item number. 

Item 1234 - Balance 0
Item 1234 - Balance 25    
Item 1234-XF - Balance 25    
Item 1234-XF - Balance 0 <-- should not be here because it is an XF and has a balance of 0.

Why doesn't this work? It does work if I do each individually, so I'm not sure why I can't combine the two.

Upvotes: 0

Views: 91

Answers (4)

Florin
Florin

Reputation: 555

Also you can use the exists operator here.

SELECT * FROM YOUR_TABLE A
WHERE balance >=0 AND NOT EXISTS(SELECT * FROM YOUR_TABLE 
WHERE A.BALANCE =0 AND A.ITEM  LIKE'%-XF').

I hope it helps.

Upvotes: 0

Mustafa Erdogan
Mustafa Erdogan

Reputation: 113

The SQL statement has a good and working result except for one value, which is XF = 0. According to the statement, it should not be there because you want only XF greater then 0.

Maybe you can try 

WHERE (Balance >= 0) OR (Item Like '%XF%' AND Balance >= 1)

Remember that 0.01 is also greater than 0

Upvotes: -1

Prometheus
Prometheus

Reputation: 56

With an OR statement in general the first statement is evaluated first and if it is true, the second statement will not be considered and in this case that record will be deemed to match the logic and be returned. The nature of your expression: (Balance >= 0) OR (Item Like '%XF%' AND Balance > 0) means that if the first is false then the second will never be true.

Try this instead:

SELECT * FROM Items WHERE (Item Like '%XF%' AND Balance > 0) OR (Balance = 0)

Upvotes: -1

Blorgbeard
Blorgbeard

Reputation: 103467

It's doing what you're asking it to do. If Balance > 0 is true, then Balance >= 0 must be true, and whether Item Like '%XF%' is true or not doesn't matter.

I think you actually want something like this:

SELECT * FROM Items 
WHERE (Balance > 0) 
   OR (Item Not Like '%XF%' AND Balance = 0)

Upvotes: 6

Related Questions