MSSQL Query Help - Long WHERE clause

ArmoVanBuuren · 3 Jul 2008 at 16:19

Code:

SELECT     *
FROM         dbo.Staff
WHERE     (Firstname LIKE '$value%') OR
              (Lastname LIKE '$value%') OR
              (FullName LIKE '$value%') AND 
              (compID <> '2')

I'm currently struggling with the above query, the query used to return the correct result but I have sinced changed the query to only show records with the compID that is not equal to 2. Now the query returns the same values as before but also shows the records with the compID of 2.

I have been doing some experimenting with the compID part of the query and that part of the query does not seem to be working at all. For example I changed the compID part to (compID = '2') and this showed 16 records when there should only be 1 displayed.

Hope I have explained this fully, if not feel free to ask any questions?

Teaf · 3 Jul 2008 at 16:42

Have you got compID in your database as text? Just from that you could try, assuming you have compID set as some kind of integer:

compID <> 2

rather than

compID <> '2'

Not sure if it's going to work or help, but it's the first thing I'd look at. I'm basing this of PHP knowledge, where wraping the value in quotes gives it the role of text, rather than not wrapping it in quotes makes the variable a number or something like boolean.

GravyMonster · 3 Jul 2008 at 17:00

You need to bracket the where clause so that it is executed in the correct order, have a try of...

Code:

SELECT     *
FROM         dbo.Staff
WHERE     ((Firstname LIKE '$value%') OR
              (Lastname LIKE '$value%') OR
              (FullName LIKE '$value%')) AND 
              (compID <> '2')

ArmoVanBuuren · 3 Jul 2008 at 17:02

Its text unfortunately and I dont have access to edit the table at the min to change the type.

Been doing some further testing by changing the query

Code:

SELECT     *
FROM         dbo.Staff
WHERE     (Firstname LIKE '$value%') OR
                      (Lastname LIKE '$value%') AND (compID <> '2')

This query works correct but whenever I try and put a 3rd OR argument like my original query the query brings up no results. So I think its something to do with too many OR?

ArmoVanBuuren · 3 Jul 2008 at 17:04

Spunkey said:
You need to bracket the where clause so that it is executed in the correct order, have a try of...

Code:

SELECT * FROM dbo.Staff WHERE ((Firstname LIKE '$value%') OR (Lastname LIKE '$value%') OR (FullName LIKE '$value%')) AND (compID <> '2')

CHEERS mate, worked a treat!!!!

Thanks