469,331 Members | 1,754 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,331 developers. It's quick & easy.

SQL Query using 'OR' Causing problems

markmcgookin
648 Expert 512MB
Hi Folks,

I am running a simple query using VB (This isnt a VB Question, dont worry!) on SQL Server Compact. I have the query below being created, and then added to if a flower location doesn't equal nothing (if it is nothing, its returning all the values) now, when this query runs and I select a flower location, i.e. L3 I want it to return all values where the flower location is equal to L3 and those where it is equal to L5.

Expand|Select|Wrap|Line Numbers
  1. statement = "SELECT * FROM tblDetails WHERE Dummy LIKE 'x'"
  2.  
  3. If flowerLocation <> "" Then
  4.                 statement = statement + " AND Location_ID LIKE '" + flowerLocation + "'"  OR Location_ID LIKE 'L5'"
  5.             End If
  6.  
But later on in my code, I have stuff like this
Expand|Select|Wrap|Line Numbers
  1. If flowerFamily <> "" Then
  2.                 statement = statement + " AND Family_ID LIKE '" + flowerFamily + "'"
  3.             End If
  4.  
  5.             If flowerHeight <> "" Then
  6.                 statement = statement + " AND Height LIKE '" + flowerHeight + "'"
  7.             End If
  8.  
Which is essentially adding to the statements,

so a possible statement could be :

Expand|Select|Wrap|Line Numbers
  1. SELECT * FROM tblDetails WHERE Dummy LIKE 'x'
  2. AND Location_ID LIKE 'L3' OR Location_ID LIKE 'L5'
  3. AND Family_ID LIKE 'F2'
  4.  
But.... my problem lies here, the query for some reason is always returning the two values that match the location even if they dont match the Family_ID

Are there issues about using an OR then an AND statement? its like the AND statement isn't "filtering" out the results returned by the 1st bit of the query.

If I take out the bit of code adding the OR Location_ID LIKE 'L5' on the code works fine, and only returns those which meet all your criteria, but it's the OR thats causing trouble, but i want to return all the values that match the location selected and those with L5, but not other locations. (NB The user can not select L5)

This is code for a search function, so the SELECT * is returning all the values in the DB and the ANDs are filtering out the stuff I dont want if the user selects something.

Hope thats clear, pls PM me if I haven't explained it well enough,

Thanks very much!!!

Mark
Apr 9 '07 #1
6 2756
iburyak
1,017 Expert 512MB
1. In VB concatenation sign is & and not +

I am not sure how this line works:

[PHP]statement = statement + " AND Family_ID LIKE '" + flowerFamily + "'"[/PHP]

I thought it should be:

[PHP]statement = statement & " AND Family_ID LIKE '" + flowerFamily + "'"[/PHP]

2. Query should have ( ) around or condition like this:

[PHP]SELECT * FROM tblDetails WHERE Dummy LIKE 'x'
AND (Location_ID LIKE 'L3' OR Location_ID LIKE 'L5')
AND Family_ID LIKE 'F2'[/PHP]

so change your statement to this:

[PHP]If flowerLocation <> "" Then
statement = statement & " AND (Location_ID LIKE '" + flowerLocation + "'" OR Location_ID LIKE 'L5')"
End If[/PHP]

Hope it helps.
Good Luck.
Apr 9 '07 #2
Mark, you just need some parentheses to group your locations. Whenever you mix ANDs and ORs you'll need to structure them a bit.

You had this:
Expand|Select|Wrap|Line Numbers
  1. SELECT * FROM tblDetails WHERE Dummy LIKE 'x'
  2. AND Location_ID LIKE 'L3' OR Location_ID LIKE 'L5'
  3. AND Family_ID LIKE 'F2'
  4.  
But you want this:
Expand|Select|Wrap|Line Numbers
  1. SELECT * 
  2. FROM tblDetails 
  3. WHERE Dummy LIKE 'x'
  4. AND ( Location_ID LIKE 'L3' OR Location_ID LIKE 'L5' )
  5. AND Family_ID LIKE 'F2'
  6.  
Apr 14 '07 #3
markmcgookin
648 Expert 512MB
Mark, you just need some parentheses to group your locations. Whenever you mix ANDs and ORs you'll need to structure them a bit.

You had this:
Expand|Select|Wrap|Line Numbers
  1. SELECT * FROM tblDetails WHERE Dummy LIKE 'x'
  2. AND Location_ID LIKE 'L3' OR Location_ID LIKE 'L5'
  3. AND Family_ID LIKE 'F2'
  4.  
But you want this:
Expand|Select|Wrap|Line Numbers
  1. SELECT * 
  2. FROM tblDetails 
  3. WHERE Dummy LIKE 'x'
  4. AND ( Location_ID LIKE 'L3' OR Location_ID LIKE 'L5' )
  5. AND Family_ID LIKE 'F2'
  6.  
Thanks for the replies folks,

I have had many AND statements in one query, and never used () before, the code I have generates a query with up to 9 ANDs and it works fine. I found a last minute workaround for the code that elimenated the need for the OR statement though. But i will go back and try with the () just for peace of mind! hehe. thanks for the help!

NB " + " works just the same as " & " in VB
Apr 14 '07 #4
Hi Mark, well it doesn't matter how many ANDs you use, it's the fact that you added an OR. :-) Throwing an OR into your boolean statement will usually require you to do some grouping to get the right behavior.

Here's my quick attempt at an pseudo code example. The first two statements define a desired outcome. Then I give a correct and incorrect way of combining them.
Expand|Select|Wrap|Line Numbers
  1. If  A=1 AND B=2 Then print "got it"
  2. If A=3 AND B=4 Then print "got it"
Taking the two statements above, you could produce the following:
Expand|Select|Wrap|Line Numbers
  1. If  (A=1 AND B=2) OR (A=3 AND B=4) Then print "got it"
Dropping the parentheses, to simply this:
Expand|Select|Wrap|Line Numbers
  1. ...A=1 AND B=2 OR A=3 AND B=4  ...
causes the statement to be interpreted as:
Expand|Select|Wrap|Line Numbers
  1. ...(A=1 AND B=2 AND B=4) OR A=3  ...
which doesn't represent the original rules.
Apr 16 '07 #5
MMcCarthy
14,534 Expert Mod 8TB
NB " + " works just the same as " & " in VB
Be careful this is not exactly true and you will run into problems using " + " for concatenation particularly when numbers are involved. You should always use " & "
Apr 17 '07 #6
markmcgookin
648 Expert 512MB
Hi Mark, well it doesn't matter how many ANDs you use, it's the fact that you added an OR. :-) Throwing an OR into your boolean statement will usually require you to do some grouping to get the right behavior.

Here's my quick attempt at an pseudo code example. The first two statements define a desired outcome. Then I give a correct and incorrect way of combining them.
Expand|Select|Wrap|Line Numbers
  1. If  A=1 AND B=2 Then print "got it"
  2. If A=3 AND B=4 Then print "got it"
Taking the two statements above, you could produce the following:
Expand|Select|Wrap|Line Numbers
  1. If  (A=1 AND B=2) OR (A=3 AND B=4) Then print "got it"
Dropping the parentheses, to simply this:
Expand|Select|Wrap|Line Numbers
  1. ...A=1 AND B=2 OR A=3 AND B=4  ...
causes the statement to be interpreted as:
Expand|Select|Wrap|Line Numbers
  1. ...(A=1 AND B=2 AND B=4) OR A=3  ...
which doesn't represent the original rules.
Aaaah, very well put. I never thought of it like that, thanks very much! Very helpful reply. I actually changed the application as a workaround for this, but that is an excellent way of putting it. If I were to go back now, I could do it like that! cheers!
Apr 22 '07 #7

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

2 posts views Thread by Stewart Allen | last post: by
6 posts views Thread by Nicolae Fieraru | last post: by
6 posts views Thread by Martin Lacoste | last post: by
5 posts views Thread by emanuel.levy | last post: by
29 posts views Thread by wizofaus | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
1 post views Thread by haryvincent176 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.