Full-Text Search 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)

Full-Text Search Query

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-04-09 : 09:30:31

Champa writes "Is there any way of implementing a full-text search query that will enable to search across columns from mutiple tables as one set of text.
For example: I have 2 tables Organisation and Products
Organisation has - OrgID, TradeName, Notes etc
Product has - ProductID, Description etc.

I am searching for something using a CONTAINS Clause..
CONTAINS(Organisation.*,'"nuts*" AND "com*"')
OR CONTAINTS(Product.*,'"nuts*" AND "com*"')
This doesn't return anything because the words 'nuts' occurs in the Product Description column of a record and the word 'com' occurs in the company Notes column or the corresponding record (for the organisation that has that product).
IS THERE ANY WAY OF SEARCHING USING THE CONTAINS SO THAT IT COMBINES THE TEXT OF BOTH THE COLUMNS AND SEARCHES ON THEM..
that way a record will get returned"

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2002-04-09 : 18:45:22

You might look into using CONTAINSTABLE instead of just CONTAINS. That way you can get a ranking back that would suggest degree of match between the two. But you haven't shown how these two tables are related (what field are they JOINed on?).

------------------------
GENERAL-ly speaking...

chocochik
Starting Member

3 Posts

Posted - 2002-04-09 : 21:12:24

The tables are structured as follows

Product(ProductID, Description) - Description is full-text indexed
Organisation(OrgID,Name,Trade Name,Address,Notes.etc) - Trade Name,Notes are full-text indexed
OrgProduct(OrgID,ProductID)

the query in its simplistic form is something like this...

SELECT * FROM Product p, Organisation o, OrgProduct op WHERE
WHERE
AND o.OrgID = op.OrgID
AND p.ProductID = op.ProductID
AND (CONTAINS(o.TradeName, @strSearch)
OR
CONTAINS(o.Notes, @strSearch)
OR
CONTAINS (p.Description, @strSearch))

So when @strSearch = '"nuts" AND "com"' no records are returned because "com" occurs in o.TradeName and "nuts" in p.Description of an Organisation record whose OrgID is related to the ProductID of the product whose description contains "nuts".
Is there any way to return that record when the above search phrase is searched upon.

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2002-04-10 : 01:24:28

OK, restating your question to see if I understand it correctly. Are you saying that you are looking for the OrgProduct (or Organization X Product combination) where the OrgProduct's related Org has 'com' in the TradeName and the same OrgProduct's related product has 'nuts' in the Description?

Isn't this as "simple" as...

SELECT *
FROM OrgProduct OP
JOIN Organisation O on OP.OrgID = O.OrgID
JOIN Product P on OP.ProductID = P.ProductID
WHERE Contains(O.*, @strSearch1)      -- * to search both Notes and TradeName
   AND Contains(P.Description, @strSearch2)

It looks like you're trying to use only one @strSearch parameter, and I don't know if that will work. Is there a reason that it would need to?

------------------------
GENERAL-ly speaking...

Edited by - AjarnMark on 04/10/2002 01:25:43

chocochik
Starting Member

3 Posts

Posted - 2002-04-11 : 00:29:47

quote:

OK, restating your question to see if I understand it correctly. Are you saying that you are looking for the OrgProduct (or Organization X Product combination) where the OrgProduct's related Org has 'com' in the TradeName and the same OrgProduct's related product has 'nuts' in the Description?

Isn't this as "simple" as...
SELECT *
FROM OrgProduct OP
JOIN Organisation O on OP.OrgID = O.OrgID
JOIN Product P on OP.ProductID = P.ProductID
WHERE Contains(O.*, @strSearch1) -- * to search both Notes and TradeName
AND Contains(P.Description, @strSearch2)

It looks like you're trying to use only one @strSearch parameter, and I don't know if that will work. Is there a reason that it would need to?

------------------------
GENERAL-ly speaking...

Edited by - AjarnMark on 04/10/2002 01:25:43

chocochik
Starting Member

3 Posts

Posted - 2002-04-11 : 00:31:16

The SQL statement above would work only if your search string was '"nuts" OR "com"' but not otherwise.
In this specific situation.. the search string is '"nuts" AND "com"'.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources