Powerful progressive search - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Powerful progressive search

Author

Topic

dasql
Starting Member

2 Posts

Posted - 2005-07-20 : 17:55:11

Hello all, I am looking to search in a progressive manner. For example:

Select ID, Name, State, Country From SearchDatabase
Where
(ID = 101 AND Name = 'Jay' AND State = 'CA' AND Country = 'USA') OR
(Name = 'Jay' AND State = 'CA' AND Country = 'USA') OR
(State = 'CA' AND Country = 'USA') OR
(Country = 'USA')

Now I have record with will match the first condition right off the bat but then I dont want to go to the other 3 conditions if I find a match for the one above. So its progressive. If I dont find any for first then goto 2nd then if nothing for 2nd then 3rd else stop at 2nd.

Thanks

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-07-20 : 19:03:05

Since those ORs will force a table scan anyway, I'd suggest:


Select ID, Name, State, Country 
From SearchDatabase
Where ID = 101 AND Name = 'Jay' AND State = 'CA' AND Country = 'USA'

set @rc = @@rowcount

if @rc = 0
begin
	Select ID, Name, State, Country From SearchDatabase
	Where 	Name = 'Jay' AND State = 'CA' AND Country = 'USA'

	set @rc = @@rowcount
end

if @rc = 0
begin
	Select ID, Name, State, Country From SearchDatabase
	Where 	State = 'CA' AND Country = 'USA'

	set @rc = @@rowcount
end

if @rc = 0
begin
	Select ID, Name, State, Country From SearchDatabase
	Where 	Country = 'USA'
end

Be One with the Optimizer
TG

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2005-07-20 : 19:08:30

You'll probably have to do it in separate statements (open to correction here, guys...):


IF EXISTS (SELECT ID FROM SearchDatabase WHERE (ID = 101 AND Name = 'Jay' AND State = 'CA' AND Country = 'USA')
    Select ID, Name, State, Country From SearchDatabase
    Where (ID = 101 AND Name = 'Jay' AND State = 'CA' AND Country = 'USA')  
ELSE
     IF EXISTS(SELECT ID FROM SearchDatabase WHERE (Name = 'Jay' AND State = 'CA' AND Country = 'USA')
          Select ID, Name, State, Country From SearchDatabase
          Where (Name = 'Jay' AND State = 'CA' AND Country = 'USA') 
     ELSE
         IF EXISTS(SELECT ID FROM SearchDatabase WHERE (State = 'CA' AND Country = 'USA')
             Select ID, Name, State, Country From SearchDatabase
             Where (State = 'CA' AND Country = 'USA') 
         ELSE 
             Select ID, Name, State, Country From SearchDatabase
             Where (Country = 'USA')

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources