Searching multiple terms and multiple tables

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)

Searching multiple terms and multiple tables

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-06-06 : 12:21:39

Randy writes "I have a SQL7 7 dbase that collects software skills/resume information submitted through the web. I then need to search, say web design (in the Talents field) + horses (from the Resume field...I'm looking for someone who is a web designer AND rides horses for a hobby). The information is stored in two tables, and I need to search one or more fields in each:

table1: Users, Fields: Name, Resume, Comments

table2: Talents (Join UserTalents to it), Fields: TalentName (search by UserID)

I am using

strSQL = "SELECT UserID, Name, Phone1, Email, Resume, Comments From Users Where Active = 1"
If Request.Form("Search") <> vbNullString Then
strSQL = strSQL & " And (Name LIKE " & AddSQLQuotes("%" & Request.Form("Search") & "%","'")
strSQL = strSQL & " OR Resume LIKE " & AddSQLQuotes("%" & Request.Form("Search") & "%","'")
strSQL = strSQL & " OR Comments LIKE " & AddSQLQuotes("%" & Request.Form("Search") & "%","'") & ")"
End If

as my query but it's not working properly.

I would greatly appreciate your help."

M.E.
Aged Yak Warrior

539 Posts

Posted - 2002-06-06 : 12:46:22

After the end if statement put

response.write strsql

This will show you next time you run the web page what exactly you are sending to your SQL server. Normally the problem is very easy to spot. If not post up the SQL your sending to your server on here and we'll go from there

--- edit
bleh, I should read more closely... You need a join statement in your from clause to join the tables together.

strSQL = "SELECT UserID, Name, Phone1, Email, Resume, Comments From Users inner join talents on users.IDcol = Talents.FKcol Where Active = 1"

Not quite sure what your keys on those table are... but hopefully you get the idea

Another option would be to just make a view that combines those 2 tables together and just run the query on the view instead. Looks a little neater in the ASP coding as well as the view could have the "where active = 1" built into it so you no longer need to worry about the where and or or statement that you have there

Edited by - M.E. on 06/06/2002 12:59:18

9000rpm
Starting Member

1 Post

Posted - 2002-06-09 : 20:59:23

Thanks for the suggestion M.E. What I'n not clear on is how to get a return of candidates that somewhere in the numerous fields searched mention both search terms. Now, if someone selects SQL developer and elsewhere says web designer, I would like their record to be found when I do a search of "web developer". Right now it won't. It only finds the exact search term. Any idea how I can get my searching to be more like Google?

M.E.
Aged Yak Warrior

539 Posts

Posted - 2002-06-10 : 11:33:39

Sure, give me a tough one. hehe...
If your given 'web developer' and you want to sql to return records such as
web designer sql developer
your going to need to split the input up.

Select * from table where column like '%web%' and column like '%developer%'

is how you'll want to send it through. Graz's parse string code will work to build your like statment. Just use ' ' as the seperator.

set @sql = 'Select * from table where'
look at grazs coding.. it well done. take the first value it gives (I think it returns them as @value)
set @sql = @sql + ' column like %'+@value+'%'
grazs query will return the rest of the values in a loop. Where he selects the value your going to put
set @sql = @sql + 'and column like %'+@value+'%'

when his parse query is done, run at the end
exec (@SQL)

and theres the parse array.
http://www.sqlteam.com/downloads/sp_parsearray.sql

any other ideas from the mroe experienced section on this one? I see possible preformance issues rising simply from all the like %% scans going on there.

-----------------------
The best answer = just do as rob or page47 say.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-06-10 : 11:44:39

quote:
The best answer = just do as rob or page47 say.

OK, stand on one leg. Reach behind you and touch your fingertips together. Then lean back as far as you can. Call paramedics when you fall over and break both your arms.

So much for THAT advice...

Actually, I do have some advice that might be helpful. No, seriously. Take a look at Merkin's keyword search article:

http://www.sqlteam.com/item.asp?ItemID=5857

M.E.
Aged Yak Warrior

539 Posts

Posted - 2002-06-10 : 12:03:48

Ouch ow ouch.

Sonofa

ya read that article, looks like exaclty what you need... I'm off to the hosptial

-----------------------
The best answer = just do as rob or page47 say.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources