Posted by Hugo Kornelis on 08/25/05 23:55

On 25 Aug 2005 04:23:44 -0700, coosa wrote:

>Sorry for "dell", it was my mistake since i had been exhaused at the
>time i was writing that last post and i appologize for that post.
>For more code illustration:
(snip)

Hi coosa,

Having the search strings in five variables makes this query lengthy.
Have you considered storing the search strings in a table? If you google
the Internet (or the newsgroups) for "Relational division", you'll find
the standard query to find sets of rows that match all rows in a second
table - you should be able to adapt those for your need.

However, the current design with five variables can be solved with this
(slow and repetitive) query:

SELECT DISTINCT v1.item_id, v1.item_name
FROM all_view AS v1
JOIN all_view AS v2
ON v2.item_id = v1.item_id
JOIN all_view AS v3
ON v3.item_id = v1.item_id
JOIN all_view AS v4
ON v4.item_id = v1.item_id
JOIN all_view AS v5
ON v5.item_id = v1.item_id
WHERE ( v1.item_name LIKE '%' + @search_key1 + '%'
OR v1.item_key LIKE '%' + @search_key1 + '%'
OR v1.item_value LIKE '%' + @search_key1 + '%')
AND ( v2.item_name LIKE '%' + @search_key2 + '%'
OR v2.item_key LIKE '%' + @search_key2 + '%'
OR v2.item_value LIKE '%' + @search_key2 + '%')
AND ( v3.item_name LIKE '%' + @search_key3 + '%'
OR v3.item_key LIKE '%' + @search_key3 + '%'
OR v3.item_value LIKE '%' + @search_key3 + '%')
AND ( v4.item_name LIKE '%' + @search_key4 + '%'
OR v4.item_key LIKE '%' + @search_key4 + '%'
OR v4.item_value LIKE '%' + @search_key4 + '%')
AND ( v5.item_name LIKE '%' + @search_key5 + '%'
OR v5.item_key LIKE '%' + @search_key5 + '%'
OR v5.item_value LIKE '%' + @search_key5 + '%')


Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

[Back to original message]