No results found for my search!

[coosa]

Dear all, I have illustared with code and sample output data my request
in thsi post. I simply was expecting some results from my search "amd
socket a 32 bit cache 512 dell" that includes a logical AND for all the
words in that search.
Since i assume that any word might be an item_name, item_key or
item_value, i included all in the search.

Can any one tell me why i get 0 results?

The sample output of my data should look like this:

item_id item_name item_key item_value
--------------------------------------------------
1 Gefore MX 440 Size 64 MB
1 Gefore MX 440 Architecture 64 Bit
1 Gefore MX 440 AGP 8x
1 Gefore MX 440 Chipset Nvidia
1 Gefore MX 440 Vendor Asus
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
3 Dell P780 Geometry 17 Inch
3 Dell P780 Screen Type Flat
3 Dell P780 Frequency 60 Hz
3 Dell P780 Vendor Dell

Here is my scenario:

create table item_table (item_id int identity (1,1) not null primary
key, item_name varchar (50) not null)
go
create table details_table (item_id int not null, item_key varchar
(50), item_value varchar (50))
go
alter table details_table add foreign key (item_id) references
item_table
go

insert into item_table values ('Gefore MX 440')
go
insert into item_table values ('AMD 3200+')
go
insert into item_table values ('Dell P780')
go

insert into details_table values (1,'Size', '64 MB')
go
insert into details_table values (1,'Architectur e', '64 Bit')
go
insert into details_table values (1,'AGP', '8x')
go
insert into details_table values (1,'Chipset', 'Nvidia')
go
insert into details_table values (1,'Vendor', 'Asus')
go

insert into details_table values (2,'Class', 'Socket A')
go
insert into details_table values (2,'Speed', '2 GHz')
go
insert into details_table values (2,'Architectur e', '32 Bit')
go
insert into details_table values (2,'Level 2 Cache', '512 KB')
go
insert into details_table values (2,'Vendor', 'AMD')
go

insert into details_table values (3,'Geometry', '17 Inch')
go
insert into details_table values (3,'Screen Type', 'Flat')
go
insert into details_table values (3,'Frequency', '60 Hz')
go
insert into details_table values (3,'Vendor', 'Dell')
go

create view all_view as
select top 100 percent i.item_id, i.item_name, d.item_key, d.item_value
from item_table as i left outer join details_table as d
on i.item_id = d.item_id
order by i.item_id, i.item_name, d.item_key, d.item_value
go

-- the complete search is "amd socket a 32 bit cache 512 dell"

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)
declare @search_key6 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'
set @search_key6 = 'dell'

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 + '%'))
and
((item_name like '%' + @search_key6 + '%') or (item_key like '%' +
@search_key6 + '%') or (item_value like '%' + @search_key6 + '%'))
go

----

Best regards

[Hugo Kornelis]

On Mon, 29 Aug 2005 17:09:48 GMT, Steve Kass wrote:

Hugo,

As usual, you have said nothing that isn't correct and
helpful here...

Hi Steve,

Thanks!
(I think.... - with double negations, I never fail to know if I didn't
prevent to misunderstand them)

Best, Hugo
--

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