help with where...

[JohnandSwifty]

Hi,

I have a query that checks the relevant fields for a match of each word in a search phrase. The problem is i need to check to see if the whole phrase matches as well. So what i want is

WHERE field01 LIKE #SearchPhrase#
OR (
field01 LIKE #SearchPhrase[1]#
AND field01 LIKE #SearchPhrase[2]#
AND field01 LIKE #SearchPhrase[3]#
)

can anyone tell me how to make this work?

Thanks

 

[JohnandSwifty]

Small ammendment (but important)

WHERE field01 LIKE #SearchPhrase#
OR field02 LIKE #SearchPhrase#
OR field03 LIKE #SearchPhrase#

OR (
field01 LIKE #SearchPhrase[1]#
OR field02 LIKE #SearchPhrase[1]#
OR field03 LIKE #SearchPhrase[1]#
AND
field01 LIKE #SearchPhrase[2]#
OR field02 LIKE #SearchPhrase[2]#
OR field03 LIKE #SearchPhrase[2]#
AND
field01 LIKE #SearchPhrase[3]#
OR field02 LIKE #SearchPhrase[3]#
OR field03 LIKE #SearchPhrase[3]#
)

 

[Crowley16]

the approach I'd take is to first split out your search phrase into a table of words, and the full phrase, then just do a where IN statement...

--------------------
Procrastinate Now!

 

[JohnandSwifty]

I need to make sure that the column has a match for each word in the phrase though. So if i use IN it will match any records that match even one of the words.

So i am trying to say where it matches the the whole phrase, or all the words in the phrase...

 

[JohnandSwifty]

Here is what my current SQL looks like -

NOTE - The second AND block is created for each word in the phrase - so to generate this code, there were two words in the phrase...

WHERE Communications.Del <> 1
AND
(
Contacts.Code LIKE (param 1)
OR Contacts.FirstName LIKE (param 2)
OR Contacts.LastName LIKE (param 3)
OR Users.Code LIKE (param 4)
OR Users.FirstName LIKE (param 5)
OR Users.LastName LIKE (param 6)
OR ClassCommunicationTypes.Code LIKE (param 7)
OR ClassCommunicationTypes.Name LIKE (param 8)
OR Communications.Code LIKE (param 9)
OR Communications.Owner LIKE (param 10)
OR Communications.Contact LIKE (param 11)
OR Communications.Method LIKE (param 12)
OR Communications.Direction LIKE (param 13)
OR Communications.Title LIKE (param 14)
OR Communications.Notes LIKE (param 15)
OR Communications.Created LIKE (param 16)
OR Communications.Status LIKE (param 17)
OR
(
Contacts.Code LIKE (param 18)
OR Contacts.FirstName LIKE (param 19)
OR Contacts.LastName LIKE (param 20)
OR Users.Code LIKE (param 21)
OR Users.FirstName LIKE (param 22)
OR Users.LastName LIKE (param 23)
OR ClassCommunicationTypes.Code LIKE (param 24)
OR ClassCommunicationTypes.Name LIKE (param 25)
OR Communications.Code LIKE (param 26)
OR Communications.Owner LIKE (param 27)
OR Communications.Contact LIKE (param 28)
OR Communications.Method LIKE (param 29)
OR Communications.Direction LIKE (param 30)
OR Communications.Title LIKE (param 31)
OR Communications.Notes LIKE (param 32)
OR Communications.Created LIKE (param 33)
OR Communications.Status LIKE (param 34)
AND
(
Contacts.Code LIKE (param 35)
OR Contacts.FirstName LIKE (param 36)
OR Contacts.LastName LIKE (param 37)
OR Users.Code LIKE (param 38)
OR Users.FirstName LIKE (param 39)
OR Users.LastName LIKE (param 40)
OR ClassCommunicationTypes.Code LIKE (param 41)
OR ClassCommunicationTypes.Name LIKE (param 42)
OR Communications.Code LIKE (param 43)
OR Communications.Owner LIKE (param 44)
OR Communications.Contact LIKE (param 45)
OR Communications.Method LIKE (param 46)
OR Communications.Direction LIKE (param 47)
OR Communications.Title LIKE (param 48)
OR Communications.Notes LIKE (param 49)
OR Communications.Created LIKE (param 50)
OR Communications.Status LIKE (param 51)
)
)
)

ORDER BY Communications.Created DESC

 

[r937]

you said "two words" but i see 51

to match all the words, change the ORs to ANDs

r937.com | rudy.ca

 

[mutley1]

Sorry guys,

I'm not one of the experts on here, but what about "full text (catalogs / indexing etc.)" and enabling it, or am I way off the mark?

 

[JohnandSwifty]

Got there in the end - i was trying to match the whole phrase and then match each word in the phrase - forgetting that by matching each word in the phrase i would already be matching the phrase! Thanks for all your help!

John