Searching for all the words

[johnsmith180]

Hi

IN SHORT:
How do you write a search query that searches for multiple words, BUT only returns a row if ALL the words are found within a row

IN DETAILS:
I have been trying to solve this problem for a long time, but with no luck. It seems very simple, but I dont have a clue how to do it.

Basically, I want to write a search query that will search in my product table. I have following table structure:

Products (productID, name, description)
OtherInfo (ID, Manufacturer, MachineName, productID)

Now a user can enter 'monitors sensors'.

I want to search in the above tables, and only return a record if 'monitors' AND 'sensors' are found for a product.

So I may write somethign like:

Select * from Products p
Inner Join OtherInfo o on p.ProductID=o.ProductID

where p.productid like ' < Search for Monitors AND Sensors >'
OR o.manufacturer like '< Search for Monitors AND Sensors >'
......

(I would like to use the LIKE operator instead of IN operator).

So Any idea how I can achieve this

I would be grateful if you can put me in the right direction.

Kind regards

 

[bborissov]

Select * from Products p
Inner Join OtherInfo o on p.ProductID=o.ProductID
where (p.productid like '%Monitors%' AND p.productid like '%Sensors%') OR 
(o.manufacturer like '%Monitors% AND o.manufacturer like '%Sensors%')

But I think the speed will be .... :-(

Borislav Borissov

 

[NoCoolHandle]

Read up on FUll TEXT searches in BooksOnLine...

They allow for a very loosly worded where clause...

lots of options..

THere are tradeoffs, but it would allow for what want.

 

[johnsmith180]

Thanks NoCoolHandle,

When I was searching in google, Full Text search did come up. However, on my 3rd party hosted SQL Server, the Full Text node is grayed out. Either this means that my hosted company does not provide 'Full Text' option OR I may have to pay extra for this service.

So would be grateful if u can suggest an alternative option.

regards