Answers to this query. - 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)

Answers to this query.

Author

Topic

magibond007
Starting Member

2 Posts

Posted - 2002-11-19 : 19:05:41

I have a table that has Names of hotels. I want to search the table for two sets of name and display them in the order. For ex :

The condition like '%Hilton%' could return

Vancouver Metrotown Hilton
Residence Inn Hilton Head Island
Ramada Inn Chilton Conference Center
Sacramento Natomas Hilton Gi
Salt Lick Hilton Safari Lodge

The condition like '%Westin%' could return

Villa Milagros Rio Mar I Westin
Westin Bellevue Dresden
The Westin Westminster
Westin Carambola Beach Resort

I want to write a query that would display the following

HotelName
-------------------------------------------
Ramada Inn Chilton Conference Center
Residence Inn Hilton Head Island
Sacramento Natomas Hilton Gi
Salt Lick Hilton Safari Lodge
Vancouver Metrotown Hilton
The Westin Westminster
Villa Milagros Rio Mar I Westin
Westin Bellevue Dresden
Westin Carambola Beach Resort

Please do not use temporary tables to do this. This has to be done in a single query

MichaelP
Jedi Yak

2489 Posts

Posted - 2002-11-19 : 19:13:15

Do a search for CSV on the sqlteam.com site. I think you'll need to pass in a CSV list of the things to search for, and then return the final result.

[rant]
I used to hate temp tables too. I used to thing they are bad, and never used them for anything. Then I discovered, they aren't bad when used properly. If you try a few different way to solve a problem similar to this, temp tables usually perform well. SQL 2000 also has table variables, which are a great alternative to Temp tables, and I use them whenever possible.

Bottom line, don't knock it till you try and test it. You'll be surprised when your numbers show that the temp table is jsut as good, if not better.
[/rant]

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-11-19 : 19:13:20

A couple of ways:

SELECT Name FROM Hotels WHERE Name LIKE '%Hilton%'
UNION ALL
SELECT Name FROM Hotels WHERE Name LIKE '%Westin%'

...or...

SELECT Name FROM Hotels WHERE Name LIKE '%Hilton%' OR Name LIKE '%Westin%'
ORDER BY CASE PatIndex('%Hilton%', Name) WHEN 0 THEN 1 ELSE 0 END

Why can't you use temp tables?

MichaelP
Jedi Yak

2489 Posts

Posted - 2002-11-19 : 19:14:42

Rob, that's EXACTLY what I had typed, but I think he'll have N "words" probably, so thus the CSV route.

What do you think? Am I off base?

[edit] I sniped you by 5 seconds....that's CLOSE :) [edit]
Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

Edited by - michaelp on 11/19/2002 19:15:16

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-11-19 : 19:17:30

Yeah, you're right, that would be the way to go with tons of search items. The problem is the ordering though, but hey, IT'S EASY TO DO THAT USING A TEMP TABLE!!!

Maybe that'll change someone's mind...

MichaelP
Jedi Yak

2489 Posts

Posted - 2002-11-19 : 19:25:34

Temp Table is the way to go IMHO. I really suggest at least trying it out. The CSV way will be dynamic SQL, which should be a bit slower than a compiled Stored Proc + Temp Table Solution. The CSV way won't be real easy to maintain either.

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-11-19 : 19:38:45

Actually, there are a couple of CSV techniques that can work without using dynamic SQL:

http://www.sqlteam.com/SearchResults.asp?SearchTerms=csv

Also, in that list, look at the "Keyword Search Using Tally Table" article, it pretty much does exactly what you want, except for the custom ordering, but it's easy to modify it to do even that.

magibond007
Starting Member

2 Posts

Posted - 2002-11-19 : 21:12:49

Here is the solution to the question.

SELECT distinct * FROM
(select top 100 percent HotelName
from Hotel where HotelName
like '%Hilton%' order by hotelName) b1
UNION ALL
select * from
(select distinct top 100 percent HotelName
from Hotel where HotelName
like '%Westin%' order by hotelName ) b2

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources