|
|
Posted by Erland Sommarskog on 08/28/05 00:51
coosa (coosa76@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, esquel@sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
Navigation:
[Reply to this message]
|