|
|
Posted by Hugo Kornelis on 06/17/05 23:59
On 17 Jun 2005 03:56:57 -0700, Axel wrote:
>My approch concatenates all candidate fields into one
>large string and then looks for the search string within that long
>string.
(snip example)
Hi Axel,
The example clarifies what you want to do.
First, revisit this page: http://www.sommarskog.se/arrays-in-sql.html.
It will explain how you can break the collected search words ("screw
iron 17 outdoor" in your example) into a set of rows.
To find the Products that match at least one of the terms given, use
SELECT DISTINCT p.Product
FROM Products AS p
INNER JOIN #SearchWords AS s -- The search words in a table;
-- See link above for details.
ON p.Name + p.Desc + p.Comment + p.Category + p.ID
LIKE '%' + s.Word + '%'
And if you only want the products that match ALL the terms given:
SELECT p.Product
FROM Products AS p
INNER JOIN #SearchWords AS s -- The search words in a table;
-- See link above for details.
ON p.Name + p.Desc + p.Comment + p.Category + p.ID
LIKE '%' + s.Word + '%'
GROUP BY p.Product
HAVING COUNT(*) = (SELECT COUNT(*) FROM #SearchWords)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
Navigation:
[Reply to this message]
|