Perform a select from keywords

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

Transact-SQL (2000)

Perform a select from keywords

Author

Topic

Stan Sainte-Rose
Starting Member

3 Posts

Posted - 2002-08-06 : 09:56:11

Hello,
I need to create a Sproc. I have 2 tables
The main called Enterprise has these fields :
Name
Idsector (int)
blabla...

The second called Sectors has these fields :
idsector (int)
keywords (ntext)

When a user enter some keywords, I need to find the idsector records from "Sectors" and find the records in "Entreprise"
Of course, I can find several idsector

Example,
In my Entreprise table I have :

Rent a Car,1
Enterpr,2
Auchan,1
Patch,1
Washcar,3

In my Sectors Table I have

1,Car Rent Drive
2,Fruit vegetables drink
3,wash car bus

If the user enter CAR as keywords I need to retrieve

Rent a Car
Washcar
Patch

Could you please help me on this topic.
I thank you in advance

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-08-06 : 10:07:23

If you are looking for an exact match, you can use the charindex function to find an occurance of the search term in the keyword list.


select
    idsector
from
    Sectors
where
    charindex(@searchterm,keywords) <> 0

You'll want to JOIN to Enterprise to get the rowset you need and you may want to do some checking for nulls in your where clause....

Jay White
{0}

Stan Sainte-Rose
Starting Member

3 Posts

Posted - 2002-08-06 : 12:56:33

Ok for this part.. I didn't have a really problem, but it's for the second part.
How to perform the final select

Something like
Select name from Enterprise Where idsector=(select idsector from Sectors where charindex(@searchterm,keywords) <> 0)

But How to translate this in the right way ?

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-08-06 : 13:02:29

Wouldn't this work?


select
    e.[name]
from
    Sectors s
    inner join Enterprise e
        on s.idsector = e.idsector
where
    charindex(@searchterm,keywords) <> 0

Jay White
{0}

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources