SQL Query using 'OR' Causing problems

[markmcgookin]

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.

statement = "SELECT * FROM tblDetails WHERE Dummy LIKE 'x'"
 
If flowerLocation <> "" Then
                statement = statement + " AND Location_ID LIKE '" + flowerLocation + "'"  OR Location_ID LIKE 'L5'"
            End If
 

But later on in my code, I have stuff like this

If flowerFamily <> "" Then
                statement = statement + " AND Family_ID LIKE '" + flowerFamily + "'"
            End If
 
            If flowerHeight <> "" Then
                statement = statement + " AND Height LIKE '" + flowerHeight + "'"
            End If
 

Which is essentially adding to the statements,

so a possible statement could be :

SELECT * FROM tblDetails WHERE Dummy LIKE 'x'
AND Location_ID LIKE 'L3' OR Location_ID LIKE 'L5'
AND Family_ID LIKE 'F2'
 

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

[iburyak]

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.

[pkreemer]

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:

SELECT * FROM tblDetails WHERE Dummy LIKE 'x'
AND Location_ID LIKE 'L3' OR Location_ID LIKE 'L5'
AND Family_ID LIKE 'F2'
 

But you want this:

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

[markmcgookin]

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:

SELECT * FROM tblDetails WHERE Dummy LIKE 'x'
AND Location_ID LIKE 'L3' OR Location_ID LIKE 'L5'
AND Family_ID LIKE 'F2'
 

But you want this:

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

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

[pkreemer]

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.

If  A=1 AND B=2 Then print "got it"
If A=3 AND B=4 Then print "got it"

Taking the two statements above, you could produce the following:

If  (A=1 AND B=2) OR (A=3 AND B=4) Then print "got it"

Dropping the parentheses, to simply this:

...A=1 AND B=2 OR A=3 AND B=4  ...

causes the statement to be interpreted as:

...(A=1 AND B=2 AND B=4) OR A=3  ...

which doesn't represent the original rules.

[MMcCarthy]

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 " & "

[markmcgookin]

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.

If  A=1 AND B=2 Then print "got it"
If A=3 AND B=4 Then print "got it"

Taking the two statements above, you could produce the following:

If  (A=1 AND B=2) OR (A=3 AND B=4) Then print "got it"

Dropping the parentheses, to simply this:

...A=1 AND B=2 OR A=3 AND B=4  ...

causes the statement to be interpreted as:

...(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!