|
|
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)
Navigation:
[Reply to this message]
|