Re: No results found for my search!

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

.

Relevant Pages