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

[Stu]

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

[coosa]

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

[Steve Kass]

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

[coosa]

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?

[coosa]

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

[coosa]

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

[Hugo Kornelis]

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)

[coosa]

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

[Steve Kass]

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

[coosa]

Ohh thanks alot steve, it worked and it runs fast!

My respect!

[coosa]

Thanks alot Steve; it worked.
Could you also please explain "Keys".
I didn't create a table or attribute called "Keys"; so where does it
come from? and what does it do?

Thanks in advance

[Erland Sommarskog]

coosa (co*****@gmail.com) writes:

Thanks alot Steve; it worked.
Could you also please explain "Keys".
I didn't create a table or attribute called "Keys"; so where does it
come from? and what does it do?

The Keys is right there in the middle of the query:

select item_id, item_name
from all_view
where not exists (
-- Begin Keys -------------------------
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
-- End 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
)
)
)

That's a "derived table". This is sort of a temp table in the middle
of the query. I say sort of, because it is never materialised, and in
fact SQL Server may rearrange the computation order, as long as the
result is preserved. This is a very powerful feature in SQL. (And it's
part of the ANSI standard and should work on most engines.)

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[coosa]

Your logic is quite interesting and i'd like to learn much from you.
First, select '%'+@search_key1+'%' or select '%'+@search_key2+'%' etc.
would bring some results, then why do you write "where NOT exists"?

Coosa

[Erland Sommarskog]

coosa (co*****@gmail.com) writes:

Your logic is quite interesting and i'd like to learn much from you.
First, select '%'+@search_key1+'%' or select '%'+@search_key2+'%' etc.
would bring some results, then why do you write "where NOT exists"?

Hey, that's not my logic, but Steve's! And there is not one NOT EXISTS -
there are two.

I will have admit that the query is a bit bewildering to me too. But if
I get right the inner NOT EXISTS filters all items where the search keys
do not match at all, and the outer NOT EXISTS then gives you the items
where there is any match. Then again, if I change one search key to Dell,
I still don't the Dell items listed.

But if I change the query to:

select item_id, item_name, item_key, item_value
from all_view
where 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 exists (
select * from all_view as V2
where V2.item_id = all_view.item_id
and (
V2.item_value like Keys.like_key or
V2.item_key like Keys.like_key
)
)
)

That is, changing NOT EXISTS to EXISTS, I still get the correct reuslt,
and Dell gets listed.

If I change one of the search keys to a non-existing value, the query with
NOT EXISTS, now give no rows back, whereas the one with EXISTS still gives
the same five rows.

Really what is the correct result, I don't know - you should know.

And really how the query really works, Steve will have to explain...

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[coosa]

sorry i mistably confused u with steve

[coosa]

sorry i confused you with steve by mistake, but thanks alot erland

[Hugo Kornelis]

On 27 Aug 2005 08:50:45 -0700, coosa wrote:

Your logic is quite interesting and i'd like to learn much from you.
First, select '%'+@search_key1+'%' or select '%'+@search_key2+'%' etc.
would bring some results, then why do you write "where NOT exists"?

Hi Coosa,

Remember back at school, when your English teacher told you: "Don't use
no double negations, not never"?

He was wrong.

I didn't take the time to go over all details in Steve's post, but at
first glance it lloks just like the "relational division" I mentioned in
my previous post.

You wanted to find items that satisfy all search terms. Steve put the
search terms in a derived table, then went on to use the standard
solution for finding a match that satisfies all rows - by rephrasing it
with a double negation:

"Find items that match all search terms" --> "Find items for which there
is no search term that it doesn't match".

Hence the use of not one but actually _TWO_ not exists conditions.

Best, Hugo
--

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

[Steve Kass]

Hugo,

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

SK

Hugo Kornelis wrote:

On 27 Aug 2005 08:50:45 -0700, coosa wrote:

Your logic is quite interesting and i'd like to learn much from you.
First, select '%'+@search_key1+'%' or select '%'+@search_key2+'%' etc.
would bring some results, then why do you write "where NOT exists"?

Hi Coosa,

Remember back at school, when your English teacher told you: "Don't use
no double negations, not never"?

He was wrong.

I didn't take the time to go over all details in Steve's post, but at
first glance it lloks just like the "relational division" I mentioned in
my previous post.

You wanted to find items that satisfy all search terms. Steve put the
search terms in a derived table, then went on to use the standard
solution for finding a match that satisfies all rows - by rephrasing it
with a double negation:

"Find items that match all search terms" --> "Find items for which there
is no search term that it doesn't match".

Hence the use of not one but actually _TWO_ not exists conditions.

Best, Hugo

[Erland Sommarskog]

Steve Kass (sk***@drew.edu) writes:

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

I note that you can't keep away from the double negatives! :-)
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

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