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,'Architecture', '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,'Architecture', '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

You don't have any one record that matches all of your criteria. Let
me try to step you through it real quick:

Your data:

> 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
>

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


Your WHERE clause:

> ((item_name like '%' + @search_key1 + '%') or (item_key like '%' +
> @search_key1 + '%') or (item_value like '%' + @search_key1 + '%'))

crieria 1 finds all rows that have AMD in them:

> 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


> and
> ((item_name like '%' + @search_key2 + '%') or (item_key like '%' +
> @search_key2 + '%') or (item_value like '%' + @search_key2 + '%'))

the use of the and further narrows down your resultset to finding rows
with the second criteria: socket a

> 2 AMD 3200+ Class Socket A

the third criteria is 32 bit, which excludes the previous row from your
result set, so you get no results.

There are much better ways to do this design, but ultimately, you're
going to have to use an OR as part of your search criteria.

HTH,
Stu

Thanks for replying, Well with OR it will work, but i think because the
relationship between item_table and details_table is 1-M so all those
many details are within one or more items. Is there a way to find
results within all of them?
Actually the item_id 2 which is "AMD 3200+" does indeed have under it
all of the details "amd socket a 32 bit cache 512 dell" except "dell"
and item_id 3 which is "Dell P780" has under it the word "dell"; how is
it possible to include all without "OR"?
My reasoning is that the user searching will assume that he will get a
result that includes "All" as a must and not optional.

Best regards

coosa wrote:
> Thanks for replying, Well with OR it will work, but i think because the
> relationship between item_table and details_table is 1-M so all those
> many details are within one or more items. Is there a way to find
> results within all of them?
> Actually the item_id 2 which is "AMD 3200+" does indeed have under it
> all of the details "amd socket a 32 bit cache 512 dell" except "dell"
> and item_id 3 which is "Dell P780" has under it the word "dell"; how is
> it possible to include all without "OR"?

If you request items matching 'dell', you will get back
items matching 'dell' only, and you won't get back item #2.

Can you please show the exact result you want, since it sounds like
you don't want the result of using AND, but you don't want the result
of using OR. But your description "find results within all of them"
is not at all precise. It would help if you gave several examples
where neither OR nor AND gives you the results you want.


Steve Kass
Drew University

> My reasoning is that the user searching will assume that he will get a
> result that includes "All" as a must and not optional.
>
> Best regards
>

Ok Steve, supposly without "dell". When I wrote that last post, i
didn't sleep since over 24 hours and i see now that "dell" was my own
mistake! :-) sorry for that.
The search would be "amd
socket a 32 bit cache 512". They are all under "AMD 3200+" which is
item_id 2. For this id those several specifications are not in one row,
but they belong to it.
I'd like a search like this with AND since i know they are all under
this id and hence it's an AND. For example, if i wrote "AMD 3200+ Speed
2 GHz" then there is a row that matches that. If i wrote then "32 Bit"
in addition to that, where "32 Bit" is under another row in the
details_table but still referes to item_id 2 and it's an AND, then
there are no results. I'd like to prevent that and make results happen!
Is this possible?

So 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)
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'

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.

Best regards

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

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)

Thanks Hugo; i will look forward to read more about "Relational
division"

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
>