Full Text Search across multiple tables (Parent-Child)

Is is possible to search across multiple tables (Parent-Child)?
contact(contact_id, name)
role(role_id, description)
contact_role (contact_id, role_id)
How can I find all contact where the search string is in the contact
name and or roles description?
I tried to search on contact then on role and join the result but it
doesn't work when I search for multiple terms ie. (smith and director)
since all terms may not appears in the parent and child table.
Thanks.
This is the quick and simplest way. You can rewrite this query using
outer-joins. The code is not tested. So expect some syntax errors.
select * from contact where contactid in
(
select contactid from contact_role where contactid in (select key from
containstable(contact,name,'search string') or roleid in (select key
from containstable(role,description,'search string')
)
martalex@.hotmail.com (Alex) wrote in message news:<71a623cb.0405031741.37e0cf5b@.posting.google. com>...
> Is is possible to search across multiple tables (Parent-Child)?
> contact(contact_id, name)
> role(role_id, description)
> contact_role (contact_id, role_id)
> How can I find all contact where the search string is in the contact
> name and or roles description?
> I tried to search on contact then on role and join the result but it
> doesn't work when I search for multiple terms ie. (smith and director)
> since all terms may not appears in the parent and child table.
> Thanks.
sql

Full Text Search

Hi,

Would below mentioned thing is possible in sqlserver 2005 full text.

User Table - Table

Id Description - Column name

1 'Mission Impossible' - a row

I want to ge this row if the search criteria is 'pos'. it is simillar to ' %pos%' in like search

is it possible to get that row using Contains() or Freetext(). I tried the following, but it doesnt return any rows.

CONTAINS(Description, ' "*pos*" ')

~ Arjun

SQLServer 2005 does not support arbitrary substring or regular expression yet. Right now you can only do prefix using fulltext.

|||

have u enabled the full text search in your server.

if so then you need not give the % symbol in the text which is to be searched. There is another option in the CONTAINS function.checkt it out.

please try this.

Ravi.

full text problem

Sorry my poor english
I am using SQL SERVER 2005 FT-enable database,
my qruestion is
some query like :
select * from test where contains(description ,'二次金改')
return 343 records--ok
try again same query
return 343 records--ok

but try 4 times later the same query,
return 0 record --stranger
and continue try is alway return 0 recode

Next day try the same query ,
return return 343 records--ok
but same situation appear again-try 4 times later the same query
return 0 record

Hi,

Which database collation are you using? Are you using a Thesaurus, and which noise word file are you using. Sorry for the questions to your questions but it helps find the problem.

Can you recreate the problem with a simple test script that you could post?

Best regards

Trevor Dwyer

|||

Thank you for replay,

database collation: Chinese_Taiwan_Stroke_CI_AS

noise word :noiseCHS.txt

I am not using aThesaurus

USE [icdb02]
GO
/****** Object: Table [dbo].[picture] Script Date: 11/05/2006 14:27:14 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[picture](
[FileID] [char](15) COLLATE Chinese_Taiwan_Stroke_CI_AS NOT NULL,
[Photographer] [varchar](50) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL,
[Place] [char](12) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL,
[PaperID] [char](1) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL,
[PictureDate] [smalldatetime] NULL,
[Description] [varchar](255) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL,
[Available] [char](1) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL,
[TimeLimit] [smalldatetime] NULL,
[DeleteDate] [smalldatetime] NULL,
[Processed] [char](1) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL,
[OperatorID] [char](15) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL,
[InputDate] [smalldatetime] NULL,
[InputID] [char](15) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL,
[rowguid] [uniqueidentifier] ROWGUIDCOL NOT NULL CONSTRAINT [DF_picture_rowguid] DEFAULT (newid()),
[picstamp] [timestamp] NULL,
CONSTRAINT [PK_picture] PRIMARY KEY CLUSTERED
(
[FileID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

Full text Pattern Search

Hi,
Would below mentioned thing is possible in sqlserver 2005 full text.
User Table - Table
Id Description - Column name
1 'Mission Impossible' - a row
I want to ge this row if the search criteria is 'pos'. it is simillar
to ' %pos%' in like search
is it possible to get that row using Contains() or Freetext(). I
tried the following, but it doesnt return any rows.
CONTAINS(Description, ' "*pos*" ')
~ Arjun
No, you can't do this directly. While you can do some suffix (end of word)
based searches, you can't do prefix (beginning of word) based searches.
It may be possible to use the thesaurus expansion feature if you know in
advance what all stem would be.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
<priyananth@.gmail.com> wrote in message
news:1160545045.049080.147130@.m73g2000cwd.googlegr oups.com...
> Hi,
> Would below mentioned thing is possible in sqlserver 2005 full text.
> User Table - Table
> Id Description - Column name
> 1 'Mission Impossible' - a row
> I want to ge this row if the search criteria is 'pos'. it is simillar
> to ' %pos%' in like search
> is it possible to get that row using Contains() or Freetext(). I
> tried the following, but it doesnt return any rows.
> CONTAINS(Description, ' "*pos*" ')
> ~ Arjun
>
|||Hi Hilary,
Thank you for u r reply. Would it be possible to modify the indexing
mechanism which does. or could you please explain how to do it in
thesaurus.
Thanks.
~Arjun.
Hilary Cotter wrote:[vbcol=seagreen]
> No, you can't do this directly. While you can do some suffix (end of word)
> based searches, you can't do prefix (beginning of word) based searches.
> It may be possible to use the thesaurus expansion feature if you know in
> advance what all stem would be.
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> <priyananth@.gmail.com> wrote in message
> news:1160545045.049080.147130@.m73g2000cwd.googlegr oups.com...
|||Hi Hilary,
Thank you for u r reply. Would it be possible to modify the indexing
mechanism which does. or could you please explain how to do it in
thesaurus.
Thanks.
~Arjun.
Hilary Cotter wrote:[vbcol=seagreen]
> No, you can't do this directly. While you can do some suffix (end of word)
> based searches, you can't do prefix (beginning of word) based searches.
> It may be possible to use the thesaurus expansion feature if you know in
> advance what all stem would be.
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> <priyananth@.gmail.com> wrote in message
> news:1160545045.049080.147130@.m73g2000cwd.googlegr oups.com...
|||You would have to write your own word breaker to do this, and then write a
stemmer to stem for prefixes. I still don't think it can be done.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
<priyananth@.gmail.com> wrote in message
news:1161075470.863928.272330@.i3g2000cwc.googlegro ups.com...
> Hi Hilary,
> Thank you for u r reply. Would it be possible to modify the indexing
> mechanism which does. or could you please explain how to do it in
> thesaurus.
> Thanks.
> ~Arjun.
>
> Hilary Cotter wrote:
>
|||I have the same problem.
It seems crippling that full text indexing does not support searching for
terms in the middle of token. Is there really that great of a demand for
searching the beginnings of words? Are there any third party products you
can recommend that will help me provide a search feature that will integrate
with SQL server?
CONTAINS term* works great but doesn't return the results I need and
'%term%' is far too slow to use in a production environment over 169000 rows
and multiple columns.
Your input is greatly appreciated.
"Hilary Cotter" wrote:

> You would have to write your own word breaker to do this, and then write a
> stemmer to stem for prefixes. I still don't think it can be done.
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> <priyananth@.gmail.com> wrote in message
> news:1161075470.863928.272330@.i3g2000cwc.googlegro ups.com...
>
>
|||You might want to look at Lucene. The story on its performance is complex.
Basically SQL FTS does a really good job and offers better performance than
Lucene on a single server when you get big. When you get big you start
needing many machines to scale using lucene. The other problem with lucence
is that when you want to do property searches you end up storing data in
your full-text index and you search response times decline radically. So you
need to partition which means more machines.
How about you return your results from sql fts and then trim them using the
like? You will need to use the thesaurus option to expand your search
criteria to all possible terms you might be looking for.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Uriah" <Uriah@.discussions.microsoft.com> wrote in message
news:521E3F54-443B-4855-9A80-5DB71C3096F2@.microsoft.com...[vbcol=seagreen]
>I have the same problem.
> It seems crippling that full text indexing does not support searching for
> terms in the middle of token. Is there really that great of a demand for
> searching the beginnings of words? Are there any third party products you
> can recommend that will help me provide a search feature that will
> integrate
> with SQL server?
> CONTAINS term* works great but doesn't return the results I need and
> '%term%' is far too slow to use in a production environment over 169000
> rows
> and multiple columns.
> Your input is greatly appreciated.
> "Hilary Cotter" wrote:
|||My goal is to have a customer searching for parts over several fields and
finding any one of several tokens anywhere within the text (similar to the
LIKE '%term%' functionality). For example, they might want to search for
'56789' and get back the part with part number 'GAR56789'. I don't see how
it is possible to do that with FTS at all. If I knew what the search terms
were going to be I could certainly pre-add them to the thesaurus but the
point of the search is that I can't predict what terms they might enter.
Maybe this is where SQL server functionality breaks down and it's time to
move on to search appliance or software platform. I'll take a look at Lucene.
"Hilary Cotter" wrote:

> You might want to look at Lucene. The story on its performance is complex.
> Basically SQL FTS does a really good job and offers better performance than
> Lucene on a single server when you get big. When you get big you start
> needing many machines to scale using lucene. The other problem with lucence
> is that when you want to do property searches you end up storing data in
> your full-text index and you search response times decline radically. So you
> need to partition which means more machines.
> How about you return your results from sql fts and then trim them using the
> like? You will need to use the thesaurus option to expand your search
> criteria to all possible terms you might be looking for.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "Uriah" <Uriah@.discussions.microsoft.com> wrote in message
> news:521E3F54-443B-4855-9A80-5DB71C3096F2@.microsoft.com...
>
>
|||If it is always a prefix you might want to store your content in reverse and
then reverse your query and do a wildcard.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Uriah" <Uriah@.discussions.microsoft.com> wrote in message
news:B695EBD3-11F8-4DEA-A81D-A1828178C577@.microsoft.com...[vbcol=seagreen]
> My goal is to have a customer searching for parts over several fields and
> finding any one of several tokens anywhere within the text (similar to the
> LIKE '%term%' functionality). For example, they might want to search for
> '56789' and get back the part with part number 'GAR56789'. I don't see
> how
> it is possible to do that with FTS at all. If I knew what the search
> terms
> were going to be I could certainly pre-add them to the thesaurus but the
> point of the search is that I can't predict what terms they might enter.
> Maybe this is where SQL server functionality breaks down and it's time to
> move on to search appliance or software platform. I'll take a look at
> Lucene.
> "Hilary Cotter" wrote:

Full Text "And Not" Fails

I have set up a simple "ProdTest1" table with 3 columns: ID (int) which is an Identity column and indexed, Name (nvarchar 45), and Description (nvarchar 2000). I have then entered a single row with the following data:
ID: 1
Name: Purple Black Blue
Description: Black Blue

I have then set up a Full-Text index based on the Name and Description columns.

When I execute the following query that row is returned in the query results:
select * from ProdTest1 where contains (*,'Black and not purple')

I would not expect any returned rows. Am I missing something?

I am using Microsoft SQL Server 2000 - 8.00.2039 (Enterprise Edition)

Thanks

Adatacorp,
Yes... You need to use double quotes for your words &/or phrases, for example:
"black" and NOT "purple" within the single quotes of the CONTAINS clause.

From BOL title "CONTAINS":
AND | AND NOT | OR - Specifies a logical operation between two contains search conditions. When <contains_search_condition> contains parenthesized groups, these parenthesized groups are evaluated first. After evaluating parenthesized groups, these rules apply when using these logical operators with contains search conditions:
NOT is applied before AND.
NOT can only occur after AND, as in AND NOT. The OR NOT operator is not allowed.
NOT cannot be specified before the first term (for example, CONTAINS (mycolumn, 'NOT "phrase_to_search_for" ' ).
AND is applied before OR.
Boolean operators of the same type (AND, OR) are associative and can therefore be applied in any order.

More specificly, try the following examples:

SELECT p.pub_id, p.pr_info, c.[rank]
from pub_info AS p,
containstable(pub_info, *, '"books" and NOT "publisher"') as c
where c.[KEY] = p.pub_id
order by c.[rank]

select pub_id, pr_info from pub_info
where contains(*,'"moon"') and NOT contains(*,'"Binnet"')

Hope that helps!
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
|||John,

Including double-quotes in the SQL does not solve the problem. Both of the following two SQL statements incorrectly return my example row:

select * from ProdTest1 where contains (*,'black and not purple')
select * from ProdTest1 where contains (*,'"black" and not "purple"')|||

How about this version? Does it return the results you are expecting?

select * from ProdTest1
where contains(*,'"black"') and NOT contains(*,'"purple"')

Additional, do you have more than one column FT-enabled? There are well known issues with searching "accross" columns when used with CONTAINS or CONTAINSTABLE, see KB article 294809 "FIX: Full-Text Search Queries with CONTAINS Clause Search Across Columns" at http://support.microsoft.com/default.aspx?scid=kb;en-us;294809 for more details.

Finally, the OS platform that you have SQL Server installed and depending upon the text in your FT-enabled column, can be an issue as well. Does your FT-enabled column contain any punctuation characters in contact with your search words? Could you post the full output of SELECT @.@.version ?

Thanks,
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/

|||

John,

You have answered the question.

I was expecting the full-text query to be applied across the concatenation of all the columns. But instead (per the KB article you reference), the query

select * from ProdTest1 where contains (*,'black and not purple')

is actually executed as...

select * from ProdTest1 where contains (Name,'black and not purple') OR contains (Description,'black and not purple')

Thus the Full-Text query correctly returns my row because the data in the Description column (Black Blue) meets the query requirement.

Indeed your suggested query:

select * from ProdTest1
where contains(*,'"black"') and NOT contains(*,'"purple"')

does return the results I desire.

However for my desired behavior in all searches I think I will need to create yet another column and in a trigger concatenate all the query column data into that added column.

I wish Microsoft had an option to simply do the Full-Text searches on the catenation of all the columns as I desire.

Thanks for your help.

|||You're welcome, Bob,
I suspected so, as you were using the "*" (astrick) that indicates to search all FT-enabled column and behavior the you are seeing is the default behavior for SQL Server 2000.

However, for SQL Server 2005 (codename Yukon) supports a new syntax of multiple column syntax, for example:

SELECT FTSPkey, FTSCol2, FTSCol3
FROM FTS_Table
WHERE CONTAINS(( FTSCol2, FTSCol3) , ' "john" AND "kane" ')

The above query is functionally equivalent to the following SQL 2000 FTS query:

SELECT FTSPkey, FTSCol2, FTSCol3
FROM FTS_Table
WHERE CONTAINS(FTSCol2, '"john" AND "kane"') OR
CONTAINS(FTSCol3, '"john" AND "kane"')

Specifically, an OR between columns is supported in a single statement, but
not an AND between columns and to the best of my knowledge NOT is not supported within this new syntax. I'll confirm this as well.

As for doing FT Searches on concatenated columns, I suspect you'll have to wait for the next version of SQL Server (post-Yukon) as IBM's DB2 Text Information Extender can do this somewhat via a UDF.

Regards,
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/