SQL "OR" operator

[PHTech]

Hi there...

I am developing a website that needs to do the following: When a user search for someone on the DB, it should look in the SURNAME collumn AND in the FIRSTNAME collumn for any matches. Like, if someone types the surname without the firstname, it should display the record(s) containing the surname AND vice versa.

My SQL is as follows:

SELECT *
FROM contacts
WHERE [Surname] OR [FirstName] = %MMColParam%

Like the SQL stated above, it displays ALL the records and won't even filter something... When using AND instead of OR it displays nothing...

AND when I change my WHERE clause to:

WHERE [Surname] = %MMColParam% OR [FirstName] = %MMColParam%

it gives me a Syntax error.... Soooooo.... I am new to MSSQL combined with ASP. MySQL worked alot easier, but am forced to do MSSQL (MS Access type)

Any help would be MUCH appreciated...

 

[foozball3000]

WHERE [Surname] = %MMColParam% OR [FirstName] = %MMColParam%

Shouldn't it be : WHERE ([Surname] = "%" + MMColParam + "%") OR ([FirstName] = "%" + MMColParam + "%")?

 

[reech]

Shouldn't it be :

WHERE (col1name LIKE "%srchterm%" OR col2name LIKE "%srchTerm%") ...

?

 

[ColinR]

I'd want to see what MMColParam resolves to.

 

[stoke]

SELECT *
FROM contacts
WHERE [Surname] OR [FirstName] = %MMColParam%

Try:

SELECT *
FROM [contacts]
WHERE ([Surname] = "%MMColParam%" or [Firstname] = "%MMColParam%")

You're missing the quotes that are required for text fields.
P.S. When using or, get into the habit of putting ( ) 's around the or logic so that if you add another condition with and, it is not gnored by the or's.
P.S. If you want to use the % wildcard, you must use like in stead of = in both cases : WHERE ([Surname] like "%MMColParam%" or [Firstname] like "%MMColParam%")

Edit : Aaah, so MMColParam is a variable, well, then give us some more info please so we can tell you how to get the value out of the variable into the SQL string.

 

[foozball3000]

Shouldn't it be : WHERE (col1name LIKE "%srchterm%" OR col2name LIKE "%srchTerm%") ...
?

I don't know if the like function has been updated, but AFAIK it's horrible... it works on a numeric system (converts characters to numbers, do some math, and gives results)..

 

[ColinR]

LIKE:

sql = "SELECT * FROM Table WHERE ([Surname] = '%" + MMColParam + "%' OR [FirstName] = '%" + MMColParam + "%')";

 

[reech]

I don't know if the like function has been updated, but AFAIK it's horrible... it works on a numeric system (converts characters to numbers, do some math, and gives results)..

I thought it did pattern matching - yes it's slow but afaik you can't use pattern matching with '=' operator?

meh - what am I saying - you can.

 

[FarligOpptreden]

If you are using Microsoft SQL Server, do it as follows:

DECLARE @PatternMatch varchar(20)
SET @PatternMatch = '%' + @MMColParam + '%'

SELECT *
FROM contacts
WHERE [Surname] LIKE @PatternMatch OR [Firstname] LIKE @PatternMatch

 

[PHTech]

I think I have managed to make it work. Here is how I have done it:

SELECT *
FROM contacts
WHERE Surname & ' ' & FirstName LIKE %MMColParam%

I have noticed that when writing ASP pages using the Microsoft Access backend driver, your SQL should be almost exactly like the SQL in MS Access... Its a bit different than the SQL statements used by PHP pages, altough the interface in Dreamweaver CS4 directly creates a universal SQL... Thats weird...

Anyways... i have seen some of you guys also questioned the %MMColParam%... It is only the Form variable and the '%' operator is the wildcard for anything before MMColParam and anything after MMColParam. Its to cancel out the search where your spelling needs to be 100% like the stuff you want to search...

BUT many thanx for all you guys that helped out on this one....!

 

[risingtide]

Is this a parametrised query?

Depending on the database used, none of the solutions given may work under all conditions. In Firebird, which is one of the most standard compliant databases it would probably have to read

SELECT *
FROM contacts
WHERE UPPER(Surname) || ' ' || COALESCE(UPPER(FirstName ), '') LIKE %:MMColParam%

Not sure about the MMColParam expression, it might just be :MMColParam and you may have to add the % signs to the passed argument.

COALESCE is necessary if FirstName IS NULL in which case the result of UPPER(Surname) || ' ' || UPPER(FirstName ) would return NULL.
And you really don't want to struggle with issues arising from Upper/Lower cased input, do you?

 

[stoke]

Cool bananas!

 

[FarligOpptreden]

Is this a parametrised query?

Depending on the database used, none of the solutions given may work under all conditions. In Firebird, which is one of the most standard compliant databases it would probably have to read

SELECT *
FROM contacts
WHERE UPPER(Surname) || ' ' || COALESCE(UPPER(FirstName ), '') LIKE %:MMColParam%

Not sure about the MMColParam expression, it might just be :MMColParam and you may have to add the % signs to the passed argument.

COALESCE is necessary if FirstName IS NULL in which case the result of UPPER(Surname) || ' ' || UPPER(FirstName ) would return NULL.
And you really don't want to struggle with issues arising from Upper/Lower cased input, do you?

NO! Coalesce is horribly slow! It doesn't filter the data, it returns the whole record set and iterates through it, matching each field to the parameters in coalesce.

Coalesce is the performance death of any database...

 

[risingtide]

You may be right, but WHERE UPPER(Surname) || ' ' || UPPER(FirstName ) LIKE
will simply return NULL if FirstName is NULL.

It may well be faster without it, but is a record set with NULL values useful?

The 'problem' may well be Firebird specific, and someone may have taken care that the application inserts an empty string instead of NULL into the FirstName field.

 

[FarligOpptreden]

You may be right, but WHERE UPPER(Surname) || ' ' || UPPER(FirstName ) LIKE
will simply return NULL if FirstName is NULL.

It may well be faster without it, but is a record set with NULL values useful?

The 'problem' may well be Firebird specific, and someone may have taken care that the application inserts an empty string instead of NULL into the FirstName field.

Well, why not test if FirstName IS NOT NULL instead? That would improve your query speed considerably... I'm serious about the speed of coalesce: We've brought the query execution speed of a specific, very complex query in our system down from 7 minutes to 1,5 seconds by just removing the COALESCE and rather building the query dynamically and executing it via sp_ExecuteSql (in MSSQL2005). You should be able to do something similar in FireBird.