Posted by Steve Kass on 08/26/05 07:08

Perhaps something like

select item_id, item_name
from all_view
where not exists (
select * from (
select '%'+@search_key1+'%' as like_key
union all select '%'+@search_key2+'%'
union all select '%'+@search_key3+'%'
union all select '%'+@search_key4+'%'
union all select '%'+@search_key5+'%'
) Keys
where not exists (
select * from all_view as V2
where V2.item_id = all_view.item_id
and (
item_value like like_key or item_key like like_key
)
)
)

SK

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:
>
>
> declare @search_key1 as varchar (50)
> declare @search_key2 as varchar (50)
> declare @search_key3 as varchar (50)
> declare @search_key4 as varchar (50)
> declare @search_key5 as varchar (50)
>
>
> set @search_key1 = 'amd'
> set @search_key2 = 'socket a'
> set @search_key3 = '32 bit'
> set @search_key4 = 'cache'
> set @search_key5 = '512'
>
>
> select distinct item_id
> from all_view
> where
> ((item_name like '%' + @search_key1 + '%') or (item_key like '%' +
> @search_key1 + '%') or (item_value like '%' + @search_key1 + '%'))
> and
> ((item_name like '%' + @search_key2 + '%') or (item_key like '%' +
> @search_key2 + '%') or (item_value like '%' + @search_key2 + '%'))
> and
> ((item_name like '%' + @search_key3 + '%') or (item_key like '%' +
> @search_key3 + '%') or (item_value like '%' + @search_key3 + '%'))
> and
> ((item_name like '%' + @search_key4 + '%') or (item_key like '%' +
> @search_key4 + '%') or (item_value like '%' + @search_key4 + '%'))
> and
> ((item_name like '%' + @search_key5 + '%') or (item_key like '%' +
> @search_key5 + '%') or (item_value like '%' + @search_key5 + '%'))
> go
>
>
> The View all_view:
>
>
> item_id item_name item_key item_value
> ------------------------------­­--------------------
> 2 AMD 3200+ Class Socket A
> 2 AMD 3200+ Speed 2 GHz
> 2 AMD 3200+ Architecture 32 Bit
> 2 AMD 3200+ Level 2 Cache 512 KB
> 2 AMD 3200+ Vendor AMD
>
>
> For all the search keys which are:
> 'amd' + 'socket a' + '32 bit' + 'cache' + '512'
> they ALL are specifications for item_id 2.
> The real result I'd like to achieve is that it executes "select
> distinct item_id" so it shows:
>
>
> item_id 2
>
>
> The user from the interface chooses "all of the words", "any of the
> words" or "exact world phrase". When he/she chooses "all of the words"
> he/she will be assuming that all of the words are mandatoray and must
> exist. Maybe my table design is bad some how, but i need suggestions.
>
> Those details in the table above, they show different rows; namely, a
> pair of key and value per id. if one row is found, other search keys
> that relate to other rows will be considered no results. Reality wise,
> those other rows do belong to that particular item as a part of the
> specifications and the user want an item that matches "all" of the keys
> he/she entered.
>
> I don't know if i illustrated enough, but looking forward to your reply
>
> Best regards
>

[Back to original message]