Full Text Table and multiple tables

I have a four join database - category as the one and the Subcategory as the
many and so on
Category
SubCategory
Announcement
SubAnnouncement
I have full text indexes on descriptive columns in the Announcement and Sub
Announcement. I would like to use a containstable or freetexttable to return
all rows in either the Announcement or SubAnnouncement that contain a
keyword but pull out the corresponding rows the subcategory and category
table. I can do the join and I can do the FreeTextTable on one table. I have
drawn a blank on putting the two together. Can anyone help.
"Chris Kennedy" <nospam@.nospam.co.uk> wrote in message
news:%23qwsWoNHEHA.3952@.TK2MSFTNGP10.phx.gbl...
> I have a four join database - category as the one and the Subcategory as
the
> many and so on
> Category
> SubCategory
> Announcement
> SubAnnouncement
> I have full text indexes on descriptive columns in the Announcement and
Sub
> Announcement. I would like to use a containstable or freetexttable to
return
> all rows in either the Announcement or SubAnnouncement that contain a
> keyword but pull out the corresponding rows the subcategory and category
> table. I can do the join and I can do the FreeTextTable on one table. I
have
> drawn a blank on putting the two together. Can anyone help.
Not sure what you're after, but CONTAINSTABLE returns a KEY column that
references the primary key of the table indexed.
SELECT *
FROM
Category AS c
JOIN Announcement AS a
ON c.catID = a.catID
JOIN CONTAINSTABLE(Announcement,description,'criterea') AS d
ON a.announceID = d.KEY
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.647 / Virus Database: 414 - Release Date: 29/03/2004
|||Unfortunately the join returns no rows e.g.
SELECT *
FROM
tblSubCategory AS s
JOIN tblAnnouncements AS a
ON s.[ID] = a.[ID]
JOIN CONTAINSTABLE(tblAnnouncements,orderkeyword,'boy') AS d
ON a.[ID] = d.[KEY]
This returns no rows
SELECT *
FROM
tblAnnouncements AS a
JOIN CONTAINSTABLE(tblAnnouncements,orderkeyword,'boy') AS d
ON a.[ID] = d.[KEY]
This does. I would have thought the join in the first table would bring in
the Category information for that row.
"Bob Simms" <bob_simms@.somewhere.com> wrote in message
news:9VZcc.35498$Id.15366@.news-binary.blueyonder.co.uk...
> "Chris Kennedy" <nospam@.nospam.co.uk> wrote in message
> news:%23qwsWoNHEHA.3952@.TK2MSFTNGP10.phx.gbl...
> the
> Sub
> return
> have
> Not sure what you're after, but CONTAINSTABLE returns a KEY column that
> references the primary key of the table indexed.
> SELECT *
> FROM
> Category AS c
> JOIN Announcement AS a
> ON c.catID = a.catID
> JOIN CONTAINSTABLE(Announcement,description,'criterea') AS d
> ON a.announceID = d.KEY
>
> --
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.647 / Virus Database: 414 - Release Date: 29/03/2004
>
|||Unfortunately the join returns no rows e.g.
SELECT *
FROM
tblSubCategory AS s
JOIN tblAnnouncements AS a
ON s.[ID] = a.[ID]
JOIN CONTAINSTABLE(tblAnnouncements,orderkeyword,'boy') AS d
ON a.[ID] = d.[KEY]
This returns no rows
SELECT *
FROM
tblAnnouncements AS a
JOIN CONTAINSTABLE(tblAnnouncements,orderkeyword,'boy') AS d
ON a.[ID] = d.[KEY]
This does.
I would have thought the join in the first query would bring in the
Category information for that row. Regards, Chris. Also any idea how
to use the contains table on tblAnnouncements and/or its subtable(
there is a subtable called tblAnnouncementData)
"Bob Simms" <bob_simms@.somewhere.com> wrote in message news:<9VZcc.35498$Id.15366@.news-binary.blueyonder.co.uk>...
> "Chris Kennedy" <nospam@.nospam.co.uk> wrote in message
> news:%23qwsWoNHEHA.3952@.TK2MSFTNGP10.phx.gbl...
> the
> Sub
> return
> have
> Not sure what you're after, but CONTAINSTABLE returns a KEY column that
> references the primary key of the table indexed.
> SELECT *
> FROM
> Category AS c
> JOIN Announcement AS a
> ON c.catID = a.catID
> JOIN CONTAINSTABLE(Announcement,description,'criterea') AS d
> ON a.announceID = d.KEY
>
> --
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.647 / Virus Database: 414 - Release Date: 29/03/2004
|||"Chris Kennedy" <chrisknospam@.cybase.co.uk> wrote in message
news:uQ2c1sTHEHA.3656@.tk2msftngp13.phx.gbl...
> Unfortunately the join returns no rows e.g.
> SELECT *
> FROM
> tblSubCategory AS s
> JOIN tblAnnouncements AS a
> ON s.[ID] = a.[ID]
> JOIN CONTAINSTABLE(tblAnnouncements,orderkeyword,'boy') AS d
> ON a.[ID] = d.[KEY]
You are joining s and a on the same name column. If they are both the
primary key of the respective tables then you are going to get unpredictable
results. You want the s.[ID] column to equal the foreign key in table a
e.g. s.[id] = a.catid
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.655 / Virus Database: 420 - Release Date: 08/04/2004

Full Text Table and multiple tables

I have a four join database - category as the one and the Subcategory as the
many and so on
Category
SubCategory
Announcement
SubAnnouncement
I have full text indexes on descriptive columns in the Announcement and Sub
Announcement. I would like to use a containstable or freetexttable to return
all rows in either the Announcement or SubAnnouncement that contain a
keyword but pull out the corresponding rows the subcategory and category
table. I can do the join and I can do the FreeTextTable on one table. I have
drawn a blank on putting the two together. Can anyone help."Chris Kennedy" <nospam@.nospam.co.uk> wrote in message
news:%23qwsWoNHEHA.3952@.TK2MSFTNGP10.phx.gbl...
> I have a four join database - category as the one and the Subcategory as
the
> many and so on
> Category
> SubCategory
> Announcement
> SubAnnouncement
> I have full text indexes on descriptive columns in the Announcement and
Sub
> Announcement. I would like to use a containstable or freetexttable to
return
> all rows in either the Announcement or SubAnnouncement that contain a
> keyword but pull out the corresponding rows the subcategory and category
> table. I can do the join and I can do the FreeTextTable on one table. I
have
> drawn a blank on putting the two together. Can anyone help.
Not sure what you're after, but CONTAINSTABLE returns a KEY column that
references the primary key of the table indexed.
SELECT *
FROM
Category AS c
JOIN Announcement AS a
ON c.catID = a.catID
JOIN CONTAINSTABLE(Announcement,description,'
criterea') AS d
ON a.announceID = d.KEY
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.647 / Virus Database: 414 - Release Date: 29/03/2004|||Unfortunately the join returns no rows e.g.
SELECT *
FROM
tblSubCategory AS s
JOIN tblAnnouncements AS a
ON s.[ID] = a.[ID]
JOIN CONTAINSTABLE(tblAnnouncements,orderkeyw
ord,'boy') AS d
ON a.[ID] = d.[KEY]
This returns no rows
SELECT *
FROM
tblAnnouncements AS a
JOIN CONTAINSTABLE(tblAnnouncements,orderkeyw
ord,'boy') AS d
ON a.[ID] = d.[KEY]
This does. I would have thought the join in the first table would bring in
the Category information for that row.
"Bob Simms" <bob_simms@.somewhere.com> wrote in message
news:9VZcc.35498$Id.15366@.news-binary.blueyonder.co.uk...
> "Chris Kennedy" <nospam@.nospam.co.uk> wrote in message
> news:%23qwsWoNHEHA.3952@.TK2MSFTNGP10.phx.gbl...
> the
> Sub
> return
> have
> Not sure what you're after, but CONTAINSTABLE returns a KEY column that
> references the primary key of the table indexed.
> SELECT *
> FROM
> Category AS c
> JOIN Announcement AS a
> ON c.catID = a.catID
> JOIN CONTAINSTABLE(Announcement,description,'
criterea') AS d
> ON a.announceID = d.KEY
>
> --
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.647 / Virus Database: 414 - Release Date: 29/03/2004
>|||Unfortunately the join returns no rows e.g.
SELECT *
FROM
tblSubCategory AS s
JOIN tblAnnouncements AS a
ON s.[ID] = a.[ID]
JOIN CONTAINSTABLE(tblAnnouncements,orderkeyw
ord,'boy') AS d
ON a.[ID] = d.[KEY]
This returns no rows
SELECT *
FROM
tblAnnouncements AS a
JOIN CONTAINSTABLE(tblAnnouncements,orderkeyw
ord,'boy') AS d
ON a.[ID] = d.[KEY]
This does.
I would have thought the join in the first query would bring in the
Category information for that row. Regards, Chris. Also any idea how
to use the contains table on tblAnnouncements and/or its subtable(
there is a subtable called tblAnnouncementData)
"Bob Simms" <bob_simms@.somewhere.com> wrote in message news:<9VZcc.35498$Id.15366@.news-bina
ry.blueyonder.co.uk>...
> "Chris Kennedy" <nospam@.nospam.co.uk> wrote in message
> news:%23qwsWoNHEHA.3952@.TK2MSFTNGP10.phx.gbl...
> the
> Sub
> return
> have
> Not sure what you're after, but CONTAINSTABLE returns a KEY column that
> references the primary key of the table indexed.
> SELECT *
> FROM
> Category AS c
> JOIN Announcement AS a
> ON c.catID = a.catID
> JOIN CONTAINSTABLE(Announcement,description,'
criterea') AS d
> ON a.announceID = d.KEY
>
> --
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.647 / Virus Database: 414 - Release Date: 29/03/2004|||"Chris Kennedy" <chrisknospam@.cybase.co.uk> wrote in message
news:uQ2c1sTHEHA.3656@.tk2msftngp13.phx.gbl...
> Unfortunately the join returns no rows e.g.
> SELECT *
> FROM
> tblSubCategory AS s
> JOIN tblAnnouncements AS a
> ON s.[ID] = a.[ID]
> JOIN CONTAINSTABLE(tblAnnouncements,orderkeyw
ord,'boy') AS d
> ON a.[ID] = d.[KEY]
You are joining s and a on the same name column. If they are both the
primary key of the respective tables then you are going to get unpredictable
results. You want the s.[ID] column to equal the foreign key in table a
e.g. s.[id] = a.catid
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.655 / Virus Database: 420 - Release Date: 08/04/2004sql

Full Text Table and multiple tables

I have a four join database - category as the one and the Subcategory as the
many and so on
Category
SubCategory
Announcement
SubAnnouncement
I have full text indexes on descriptive columns in the Announcement and Sub
Announcement. I would like to use a containstable or freetexttable to return
all rows in either the Announcement or SubAnnouncement that contain a
keyword but pull out the corresponding rows the subcategory and category
table. I can do the join and I can do the FreeTextTable on one table. I have
drawn a blank on putting the two together. Can anyone help."Chris Kennedy" <nospam@.nospam.co.uk> wrote in message
news:%23qwsWoNHEHA.3952@.TK2MSFTNGP10.phx.gbl...
> I have a four join database - category as the one and the Subcategory as
the
> many and so on
> Category
> SubCategory
> Announcement
> SubAnnouncement
> I have full text indexes on descriptive columns in the Announcement and
Sub
> Announcement. I would like to use a containstable or freetexttable to
return
> all rows in either the Announcement or SubAnnouncement that contain a
> keyword but pull out the corresponding rows the subcategory and category
> table. I can do the join and I can do the FreeTextTable on one table. I
have
> drawn a blank on putting the two together. Can anyone help.
Not sure what you're after, but CONTAINSTABLE returns a KEY column that
references the primary key of the table indexed.
SELECT *
FROM
Category AS c
JOIN Announcement AS a
ON c.catID = a.catID
JOIN CONTAINSTABLE(Announcement,description,'criterea') AS d
ON a.announceID = d.KEY
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.647 / Virus Database: 414 - Release Date: 29/03/2004|||Unfortunately the join returns no rows e.g.
SELECT *
FROM
tblSubCategory AS s
JOIN tblAnnouncements AS a
ON s.[ID] = a.[ID]
JOIN CONTAINSTABLE(tblAnnouncements,orderkeyword,'boy') AS d
ON a.[ID] = d.[KEY]
This returns no rows
SELECT *
FROM
tblAnnouncements AS a
JOIN CONTAINSTABLE(tblAnnouncements,orderkeyword,'boy') AS d
ON a.[ID] = d.[KEY]
This does. I would have thought the join in the first table would bring in
the Category information for that row.
"Bob Simms" <bob_simms@.somewhere.com> wrote in message
news:9VZcc.35498$Id.15366@.news-binary.blueyonder.co.uk...
> "Chris Kennedy" <nospam@.nospam.co.uk> wrote in message
> news:%23qwsWoNHEHA.3952@.TK2MSFTNGP10.phx.gbl...
> > I have a four join database - category as the one and the Subcategory as
> the
> > many and so on
> >
> > Category
> > SubCategory
> > Announcement
> > SubAnnouncement
> >
> > I have full text indexes on descriptive columns in the Announcement and
> Sub
> > Announcement. I would like to use a containstable or freetexttable to
> return
> > all rows in either the Announcement or SubAnnouncement that contain a
> > keyword but pull out the corresponding rows the subcategory and category
> > table. I can do the join and I can do the FreeTextTable on one table. I
> have
> > drawn a blank on putting the two together. Can anyone help.
> Not sure what you're after, but CONTAINSTABLE returns a KEY column that
> references the primary key of the table indexed.
> SELECT *
> FROM
> Category AS c
> JOIN Announcement AS a
> ON c.catID = a.catID
> JOIN CONTAINSTABLE(Announcement,description,'criterea') AS d
> ON a.announceID = d.KEY
>
> --
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.647 / Virus Database: 414 - Release Date: 29/03/2004
>|||Unfortunately the join returns no rows e.g.
SELECT *
FROM
tblSubCategory AS s
JOIN tblAnnouncements AS a
ON s.[ID] = a.[ID]
JOIN CONTAINSTABLE(tblAnnouncements,orderkeyword,'boy') AS d
ON a.[ID] = d.[KEY]
This returns no rows
SELECT *
FROM
tblAnnouncements AS a
JOIN CONTAINSTABLE(tblAnnouncements,orderkeyword,'boy') AS d
ON a.[ID] = d.[KEY]
This does.
I would have thought the join in the first query would bring in the
Category information for that row. Regards, Chris. Also any idea how
to use the contains table on tblAnnouncements and/or its subtable(
there is a subtable called tblAnnouncementData)
"Bob Simms" <bob_simms@.somewhere.com> wrote in message news:<9VZcc.35498$Id.15366@.news-binary.blueyonder.co.uk>...
> "Chris Kennedy" <nospam@.nospam.co.uk> wrote in message
> news:%23qwsWoNHEHA.3952@.TK2MSFTNGP10.phx.gbl...
> > I have a four join database - category as the one and the Subcategory as
> the
> > many and so on
> >
> > Category
> > SubCategory
> > Announcement
> > SubAnnouncement
> >
> > I have full text indexes on descriptive columns in the Announcement and
> Sub
> > Announcement. I would like to use a containstable or freetexttable to
> return
> > all rows in either the Announcement or SubAnnouncement that contain a
> > keyword but pull out the corresponding rows the subcategory and category
> > table. I can do the join and I can do the FreeTextTable on one table. I
> have
> > drawn a blank on putting the two together. Can anyone help.
> Not sure what you're after, but CONTAINSTABLE returns a KEY column that
> references the primary key of the table indexed.
> SELECT *
> FROM
> Category AS c
> JOIN Announcement AS a
> ON c.catID = a.catID
> JOIN CONTAINSTABLE(Announcement,description,'criterea') AS d
> ON a.announceID = d.KEY
>
> --
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.647 / Virus Database: 414 - Release Date: 29/03/2004|||"Chris Kennedy" <chrisknospam@.cybase.co.uk> wrote in message
news:uQ2c1sTHEHA.3656@.tk2msftngp13.phx.gbl...
> Unfortunately the join returns no rows e.g.
> SELECT *
> FROM
> tblSubCategory AS s
> JOIN tblAnnouncements AS a
> ON s.[ID] = a.[ID]
> JOIN CONTAINSTABLE(tblAnnouncements,orderkeyword,'boy') AS d
> ON a.[ID] = d.[KEY]
You are joining s and a on the same name column. If they are both the
primary key of the respective tables then you are going to get unpredictable
results. You want the s.[ID] column to equal the foreign key in table a
e.g. s.[id] = a.catid
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.655 / Virus Database: 420 - Release Date: 08/04/2004

full text search with language other than english(ex chinese, japanese)

I have set up a full text search to handle multiple columns searching
for chinese
But the result of the search isn't really what i have expected.
I have setup the catalog to have a chiense word break, and the columns
in the tables are all nvachar
when i do something like
select * from dbo.Table_1 where contains(*, '"<chinese
character>"',language 1082)
the search result is really inconsistent, especially with single
characters.I have also checked that these characters are not in the
noise filter file...
the search result is better when the input is more than a single
characters, but still, somtimes it will not return any result at all.
so, I try to use the "like" statement instead of "contains" to do the
search with the same inputs, and 100% of the time, it returns the
correct result.
does anyone have any experience about things like that? coz I guess
this is a more spcific issue with language. Is there any place that
you guys know of, can offer me some help?
thank you in advance.
On Feb 11, 5:33Xam, "Hilary Cotter" <hilary.cot...@.gmail.com> wrote:
> The Chinese word breaker does multiple passes on characters pulling out characters to try figure out which characters go together and which ones should be treated as a single "word". It even pulls out radicals.
> However, try this on the thishttp://search.microsoft.com/results.aspx?q=%E6%9F%90%E4%BA%BA%E7%82%B...
> searching on 某人為了商X或個人使用.
> Then try 某人為了商X或個人使 (note I have removed the last character)
> http://search.microsoft.com/results.aspx?q=%E6%9F%90%E4%BA%BA%E7%82%B...
> Nothing found.
> So while it works well in general, there are some inconsistencies.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTShttp://www.indexserverfaq.com
>
> <admin.on...@.gmail.com> wrote in messagenews:1171171251.557135.10240@.h3g2000cwc.goo glegroups.com...
>
>
>
>
>
> - Show quoted text -
icic
oh Thank you, so there is no way to improve that ?
thank you
|||On Feb 11, 5:33Xam, "Hilary Cotter" <hilary.cot...@.gmail.com> wrote:
> The Chinese word breaker does multiple passes on characters pulling out characters to try figure out which characters go together and which ones should be treated as a single "word". It even pulls out radicals.
> However, try this on the thishttp://search.microsoft.com/results.aspx?q=%E6%9F%90%E4%BA%BA%E7%82%B...
> searching on 某人為了商X或個人使用.
> Then try 某人為了商X或個人使 (note I have removed the last character)
> http://search.microsoft.com/results.aspx?q=%E6%9F%90%E4%BA%BA%E7%82%B...
> Nothing found.
> So while it works well in general, there are some inconsistencies.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTShttp://www.indexserverfaq.com
>
> <admin.on...@.gmail.com> wrote in messagenews:1171171251.557135.10240@.h3g2000cwc.goo glegroups.com...
>
>
>
>
>
> - Show quoted text -
I hit the send button too quickly for the last post...
coz I tried out a couple of single character that my search couldn't
handle in http://forums.asp.net, and it acutally can return results
there...
i am just wondering if there is any other settings i can try to
improve that ?
thank you
|||You may have better success using the Traditional word breaker.
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
<admin.onQhk@.gmail.com> wrote in message
news:1171225568.325092.171990@.h3g2000cwc.googlegro ups.com...
On Feb 11, 5:33 am, "Hilary Cotter" <hilary.cot...@.gmail.com> wrote:
> The Chinese word breaker does multiple passes on characters pulling out
> characters to try figure out which characters go together and which ones
> should be treated as a single "word". It even pulls out radicals.
> However, try this on the
> thishttp://search.microsoft.com/results.aspx?q=%E6%9F%90%E4%BA%BA%E7%82%B...
> searching on ???.
> Then try ?? (note I have removed the last character)
> http://search.microsoft.com/results.aspx?q=%E6%9F%90%E4%BA%BA%E7%82%B...
> Nothing found.
> So while it works well in general, there are some inconsistencies.
> --
> Hilary Cotter
> Looking for a SQL Server replication
> book?http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL
> FTShttp://www.indexserverfaq.com
>
> <admin.on...@.gmail.com> wrote in
> messagenews:1171171251.557135.10240@.h3g2000cwc.goo glegroups.com...
>
>
>
>
>
> - Show quoted text -
icic
oh Thank you, so there is no way to improve that ?
thank you
|||They may be doing a like.
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
<admin.onQhk@.gmail.com> wrote in message
news:1171226670.382397.154660@.k78g2000cwa.googlegr oups.com...
On Feb 11, 5:33 am, "Hilary Cotter" <hilary.cot...@.gmail.com> wrote:
> The Chinese word breaker does multiple passes on characters pulling out
> characters to try figure out which characters go together and which ones
> should be treated as a single "word". It even pulls out radicals.
> However, try this on the
> thishttp://search.microsoft.com/results.aspx?q=%E6%9F%90%E4%BA%BA%E7%82%B...
> searching on ???.
> Then try ?? (note I have removed the last character)
> http://search.microsoft.com/results.aspx?q=%E6%9F%90%E4%BA%BA%E7%82%B...
> Nothing found.
> So while it works well in general, there are some inconsistencies.
> --
> Hilary Cotter
> Looking for a SQL Server replication
> book?http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL
> FTShttp://www.indexserverfaq.com
>
> <admin.on...@.gmail.com> wrote in
> messagenews:1171171251.557135.10240@.h3g2000cwc.goo glegroups.com...
>
>
>
>
>
> - Show quoted text -
I hit the send button too quickly for the last post...
coz I tried out a couple of single character that my search couldn't
handle in http://forums.asp.net, and it acutally can return results
there...
i am just wondering if there is any other settings i can try to
improve that ?
thank you

full text search with language other than english(ex chinese, japanese)

I have set up a full text search to handle multiple columns searching for chinese

But the result of the search isn't really what i have expected.

I have setup the catalog to have a chiense word break, and the columns in the tables are all nvachar

when i do something like

select * from dbo.Table_1 where contains(*, '"<chinese character>"',language 1082)

the search result is really inconsistent, especially with single characters.I have also checked that these characters are not in the noise filter file...

the search result is better when the input is more than a single characters, but still, somtimes it will not return any result at all.

so, I try to use the "like" statement instead of "contains" to do the search with the same inputs, and 100% of the time, it returns the correct result.

does anyone have any experience about things like that? coz I guess this is a more spcific issue with language. Is there any place that you guys know of, can offer me some help?

thank you in advance.

I think you need to use the correct Chinese collation instead of just Nvarchar because Nvarchar just tells SQL Server you are not using ASCII but Chinese and Japanese alphabet are more than two thousand characters while Latin is just 26. There are six Chinese and I think three Japanese collations defined in SQL Server, try the link below to choose your collation. I also think you need to make sure your Microsoft search catalog is populated and check the noise word file. Run a search for noise words and Microsoft search catalog in SQL Server BOL(books online). Hope this helps.

http://msdn2.microsoft.com/en-us/library/ms180175.aspx

|||

Thank you so much

But after trying lots... of different collation settings, it still yields the same results...
Is there any more think that I can try ?

thank you

|||

Out of cuorcuriosity, I try to search a single chinese character which my search couldn't handle in this asp.net forums. This forum's search acutally returns result ...
so there must be something wrong that I am doing coz I really doubt that they will do anything special in this forum to handle chinese characters specifically......

just wondering if there is anything else that i could try ??

thank you in advance.....

full text search on multiple tables yielding one rank

Is it possible to do full text search on multiple tables and having one rank
for all searches? Ive read a lot of articles that gives me code samples on
doing FTS on multi tables but they gave me multiple ranks for each search.
Without consolidating all of your data in the multiple tables into a child
table there is no way to do this with a meaningful rank.
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
"surigaonon" <surigaonon@.discussions.microsoft.com> wrote in message
news:28625427-A482-40B3-991B-BC7C35501801@.microsoft.com...
> Is it possible to do full text search on multiple tables and having one
> rank
> for all searches? Ive read a lot of articles that gives me code samples on
> doing FTS on multi tables but they gave me multiple ranks for each search.
sql

Full text search multiple tables

Hi all,
I want to full text search on multiple tables.
Example my strSearch = example full text search engine.

"SELECT TOP (100) PERCENT FT_TBL.Description AS mota, FT_TBL.test1_ID, KEY_TBL.RANK
FROM dbo.test1 AS FT_TBL INNER JOIN
CONTAINSTABLE(test1, *, '"+strSearch+"') AS KEY_TBL ON FT_TBL.test1_ID = KEY_TBL.[KEY]
UNION ALL
SELECT TOP (100) PERCENT FT_TBL1.Description1 AS mota, FT_TBL1.test2_ID, KEY_TBL1.RANK
FROM dbo.test2 AS FT_TBL1 INNER JOIN
CONTAINSTABLE(test2, *, '"+strSearch+"') AS KEY_TBL1 ON FT_TBL1.test2_ID = KEY_TBL1.[KEY]"

I want to show results order such as GOOGLE: show top records full keyword "example full text search engine", then continune left phrases.

pls help me

You want to try freetexttable. If all words are found in a row, the row is given higher rank. However, there is no garantee that the full phrase is getting better scan than a partial match (for example, if there are several partial matches found)

If you want a match exactly the phrase, you need to do a phrase search. Probably you want to do a phrase search union freetext table?

Rank from different tables generally are not comparable. You can create an indexed view of the two table and build fulltext index over the indexed view to get comparable ranking.

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 across multiple tables

Imagine you're building a search for a movie database which you wanted to
index actors, quotes from scenes and movie names into a single search... how
do you accomplish this using full text search? The closest I can think of
is... (for a sample search for "Walken"
select RANK, actorId as Id from FREETEXTTABLE( actors, *, 'ISABOUT (+ WALKEN
+ WEIGHT(1.0))') a JOIN actors b on a.[key] = b.actorid
UNION ALL
select RANK, sceneId as Id from FREETEXTTABLE( scenes, *, 'ISABOUT (+ WALKEN
+ WEIGHT(1.0))') a JOIN scenes b on a.[key] = b.sceneId
UNION ALL
select RANK, movieId as Id from FREETEXTTABLE( movie, *, 'ISABOUT (+ WALKEN
+ WEIGHT(1.0))') a JOIN movie b on a.[key] = b.movieId
But it doesn't rank correctly. Suggestions? I was thinking of creating a
view to combine all the tables, but that doesn't work either, since the view
wouldn't have a unique column (which FTS needs)
Iron,
If I understand your question this might help. I've done this before with
company names, addresses and phone numbers and other company information
contained in multiple tables.
1. Create another table with a column that concatinates all the columns you
want to index. It would require a composite primary key of actorid, sceneid,
and movieid. I am assuming that this relationship exists in your database.
2. Configure your full text catalog to work with the new table and column.
3. Populate the new table.
4. Apply your full text queries against the new table and join to the other
tables to return the recordsets.
I duplicates data, but it works great.
-- Bill
"IronYuppie" <IronYuppie@.discussions.microsoft.com> wrote in message
news:E5C1BB12-8C96-4EE2-81CA-9157E67C388E@.microsoft.com...
> Imagine you're building a search for a movie database which you wanted to
> index actors, quotes from scenes and movie names into a single search...
> how
> do you accomplish this using full text search? The closest I can think of
> is... (for a sample search for "Walken"
> select RANK, actorId as Id from FREETEXTTABLE( actors, *, 'ISABOUT (+
> WALKEN
> + WEIGHT(1.0))') a JOIN actors b on a.[key] = b.actorid
> UNION ALL
> select RANK, sceneId as Id from FREETEXTTABLE( scenes, *, 'ISABOUT (+
> WALKEN
> + WEIGHT(1.0))') a JOIN scenes b on a.[key] = b.sceneId
> UNION ALL
> select RANK, movieId as Id from FREETEXTTABLE( movie, *, 'ISABOUT (+
> WALKEN
> + WEIGHT(1.0))') a JOIN movie b on a.[key] = b.movieId
> But it doesn't rank correctly. Suggestions? I was thinking of creating a
> view to combine all the tables, but that doesn't work either, since the
> view
> wouldn't have a unique column (which FTS needs)

Full Text Search across multiple tables

Imagine you're building a search for a movie database which you wanted to
index actors, quotes from scenes and movie names into a single search... ho
w
do you accomplish this using full text search? The closest I can think of
is... (for a sample search for "Walken"
select RANK, actorId as Id from FREETEXTTABLE( actors, *, 'ISABOUT (+ WALKEN
+ WEIGHT(1.0))') a JOIN actors b on a.[key] = b.actorid
UNION ALL
select RANK, sceneId as Id from FREETEXTTABLE( scenes, *, 'ISABOUT (+ WALKEN
+ WEIGHT(1.0))') a JOIN scenes b on a.[key] = b.sceneId
UNION ALL
select RANK, movieId as Id from FREETEXTTABLE( movie, *, 'ISABOUT (+ WALKEN
+ WEIGHT(1.0))') a JOIN movie b on a.[key] = b.movieId
But it doesn't rank correctly. Suggestions? I was thinking of creating a
view to combine all the tables, but that doesn't work either, since the view
wouldn't have a unique column (which FTS needs)Iron,
If I understand your question this might help. I've done this before with
company names, addresses and phone numbers and other company information
contained in multiple tables.
1. Create another table with a column that concatinates all the columns you
want to index. It would require a composite primary key of actorid, sceneid,
and movieid. I am assuming that this relationship exists in your database.
2. Configure your full text catalog to work with the new table and column.
3. Populate the new table.
4. Apply your full text queries against the new table and join to the other
tables to return the recordsets.
I duplicates data, but it works great.
-- Bill
"IronYuppie" <IronYuppie@.discussions.microsoft.com> wrote in message
news:E5C1BB12-8C96-4EE2-81CA-9157E67C388E@.microsoft.com...
> Imagine you're building a search for a movie database which you wanted to
> index actors, quotes from scenes and movie names into a single search...
> how
> do you accomplish this using full text search? The closest I can think of
> is... (for a sample search for "Walken"
> select RANK, actorId as Id from FREETEXTTABLE( actors, *, 'ISABOUT (+
> WALKEN
> + WEIGHT(1.0))') a JOIN actors b on a.[key] = b.actorid
> UNION ALL
> select RANK, sceneId as Id from FREETEXTTABLE( scenes, *, 'ISABOUT (+
> WALKEN
> + WEIGHT(1.0))') a JOIN scenes b on a.[key] = b.sceneId
> UNION ALL
> select RANK, movieId as Id from FREETEXTTABLE( movie, *, 'ISABOUT (+
> WALKEN
> + WEIGHT(1.0))') a JOIN movie b on a.[key] = b.movieId
> But it doesn't rank correctly. Suggestions? I was thinking of creating a
> view to combine all the tables, but that doesn't work either, since the
> view
> wouldn't have a unique column (which FTS needs)

full text search : Search orders of single input of "multiple word

Sql server express advanced edition
Hi
I am using following query to select data using full text search in a stored
procedure:
=====================
SET @.keyword = '"*' + LOWER(@.keyword) + '*"'
Select *
from tablename1
where contains (*, @.keyword)
===========================
It returns me rows for keyword "Fish Chips" which have values (Fish and
chips) but when i change my search keyword to "Chips Fish" it does not return
any rows.
Can I modify my query so that It returms me all the rows containing Fish or
chips in any order?
i.e. In the input screen for users they input a combination of words like
"chinese mexican". My query should spit all rows which have chinese and
mexican in any order in one of indexed columns.
For above input (Chinese mexican) I should have output like :
Chinese mexican food
chinese and mexican food
mexican and chinese food
mexican chinese clothing
mexican food indian food and chinese food
If I need to parse input and divide it in multiple words before searching,
If someone has a example on how to do this, will be great.
ontario, canada
In other words, I have requirement where I need to do full text search on
all the
search text. For example, for the search text "fish chips" , I should get
the following records having "fish chips" as well as "chips fish".
ontario, canada
"db" wrote:

> Sql server express advanced edition
> Hi
> I am using following query to select data using full text search in a stored
> procedure:
> =====================
> SET @.keyword = '"*' + LOWER(@.keyword) + '*"'
> Select *
> from tablename1
> where contains (*, @.keyword)
> ===========================
> It returns me rows for keyword "Fish Chips" which have values (Fish and
> chips) but when i change my search keyword to "Chips Fish" it does not return
> any rows.
> Can I modify my query so that It returms me all the rows containing Fish or
> chips in any order?
> i.e. In the input screen for users they input a combination of words like
> "chinese mexican". My query should spit all rows which have chinese and
> mexican in any order in one of indexed columns.
> For above input (Chinese mexican) I should have output like :
> Chinese mexican food
> chinese and mexican food
> mexican and chinese food
> mexican chinese clothing
> mexican food indian food and chinese food
> If I need to parse input and divide it in multiple words before searching,
> If someone has a example on how to do this, will be great.
>
> --
> ontario, canada

FTS newbie - Searching accross multiple tables using CONTAINSTABLE

Hi all
I have a database with a number of tables with related information. I would
like to do a search across all these tables using AND, OR etc, which means
using CONTAINSTABLE, right?
Using something like:
searchStr = "dog AND cat"
SELECT a FROM parenttable
WHERE parentid IN
(SELECT [KEY] FROM CONTAINSTABLE(childtable1, *, searchStr))
OR parentid IN
(SELECT [KEY] FROM CONTAINSTABLE(childtable2, *, searchStr))
I get some results... The problem is, of course, that this means that both
dog and cat have to be in each child table, but I want to get all parent
table rows where dog AND cat appear ANYWHERE in the child tables (eg cat in
one and dog in the other, or both in one child table and none in the other,
and so on). Is this possible? Any suggestions?
This would be used on a search page on an ASP site. The real tables are more
like grandparent - parent - child, child, child.
Thanks a lot in advance!
Marcus
does the key column correspond to the pk of the parent table?
If so, this should work.
|||Thanks for the reply!
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:3BEBA99D-395A-4186-AAC6-6F22F7FE9E28@.microsoft.com...
> does the key column correspond to the pk of the parent table?
> If so, this should work.
Yes, the key column corresponds to the pk of the parent, but it doesn't
work. I am testing on some records where all the rows of child1 contain
"cat" and all the rows of child2 contain "dog". If I set the search string
to "cat" I get all the parent rows, like I should. Likewise, I get all rows
if I set the search string to "dog", of course. However, if I set the search
string to "cat AND dog" I get no rows.
This is not particularly surprising to me, since each "child query" executed
independently returns zero keys for "cat AND dog". Am I missing something
here? What can I do?
Here's the query again, for readability:
SELECT a FROM parenttable
WHERE parentid IN
(SELECT [KEY] FROM CONTAINSTABLE(childtable1, *, searchStr))
OR parentid IN
(SELECT [KEY] FROM CONTAINSTABLE(childtable2, *, searchStr))
Thank you!
Regards,
Marcus
|||No replies... So I take it this is not possible? Any suggestions on other
solutions?
Thanks,
Marcus
"Marcus" <lumbus@.ludd.luth.se> wrote in message
news:uXlNUDROEHA.2876@.TK2MSFTNGP09.phx.gbl...
> Thanks for the reply!
> "Hilary Cotter" <hilaryk@.att.net> wrote in message
> news:3BEBA99D-395A-4186-AAC6-6F22F7FE9E28@.microsoft.com...
> Yes, the key column corresponds to the pk of the parent, but it doesn't
> work. I am testing on some records where all the rows of child1 contain
> "cat" and all the rows of child2 contain "dog". If I set the search string
> to "cat" I get all the parent rows, like I should. Likewise, I get all
rows
> if I set the search string to "dog", of course. However, if I set the
search
> string to "cat AND dog" I get no rows.
> This is not particularly surprising to me, since each "child query"
executed
> independently returns zero keys for "cat AND dog". Am I missing something
> here? What can I do?
> Here's the query again, for readability:
> SELECT a FROM parenttable
> WHERE parentid IN
> (SELECT [KEY] FROM CONTAINSTABLE(childtable1, *, searchStr))
> OR parentid IN
> (SELECT [KEY] FROM CONTAINSTABLE(childtable2, *, searchStr))
> Thank you!
> Regards,
> Marcus
>
|||Marcus,
I had assumed that from your previous "Thanks for the reply" reply that the
[Primary] key (PK) column corresponds to the PK in the parent table and
therefore is solution was viable for you. Is that not correct? If so, then
could you provide more details on your table structures and the real SQL FTS
query?
Thanks,
John
"Marcus" <lumbus@.ludd.luth.se> wrote in message
news:uBT$PwdPEHA.4036@.TK2MSFTNGP12.phx.gbl...[vbcol=seagreen]
> No replies... So I take it this is not possible? Any suggestions on other
> solutions?
> Thanks,
> Marcus
> "Marcus" <lumbus@.ludd.luth.se> wrote in message
> news:uXlNUDROEHA.2876@.TK2MSFTNGP09.phx.gbl...
string[vbcol=seagreen]
> rows
> search
> executed
something
>
|||John,
Thanks for the reply. Either I'm missing something here or there is some
confusion about what I'm looking for. I'll try to explain this very clearly:
My real tables are pretty complicated and I think they will only confuse. I
have made a test database that looks like this:
TABLE [Parent]:
[ParentID] [int] IDENTITY (1, 1) NOT NULL
[Data] [varchar] (100)
TABLE [Child1]:
[ParentID] [int] NOT NULL ,
[Data] [varchar] (100)
TABLE [Child2]:
[ParentID] [int] NOT NULL ,
[Data] [varchar] (100)
I then insert the following rows:
TABLE [Parent]:
ParentID=1, Data='dummy'
TABLE [Child1]:
ParentID=1, Data='the dog says woof'
TABLE [Child2]:
ParentID=1, Data='the cat says miau'
Now, remember: What I want to do is to search for "dog AND cat" in this
structure, returning the IDs for the parent rows where the words "cat and
"dog" appear anywhere in the tables, for example "cat" in Child1 and "dog"
in Child2.
This query...
SELECT ParentID FROM Parent
WHERE ParentID IN
(SELECT [KEY] FROM CONTAINSTABLE(Child1, *, 'cat AND dog'))
OR ParentID IN
(SELECT [KEY] FROM CONTAINSTABLE(Child2, *, 'cat AND dog'))
...returns nothing. This is the problem I'm asking about.
Just to show that the tables are healthy, this query...
SELECT ParentID FROM Parent
WHERE ParentID IN
(SELECT [KEY] FROM CONTAINSTABLE(Child1, *, 'cat OR dog'))
OR ParentID IN
(SELECT [KEY] FROM CONTAINSTABLE(Child2, *, 'cat OR dog'))
...returns ParentID=1. And these queries...
SELECT [KEY] FROM CONTAINSTABLE(Child1, *, 'cat OR dog')
SELECT [KEY] FROM CONTAINSTABLE(Child2, *, 'cat OR dog')
...return KEY=1.
So the question is whether there is a solution to this or whether I should
give up on using FTS for my search functionality.
Thank you!
Regards,
Marcus
"John Kane" <jt-kane@.comcast.net> wrote in message
news:Op2OC3gPEHA.3524@.TK2MSFTNGP09.phx.gbl...
> Marcus,
> I had assumed that from your previous "Thanks for the reply" reply that
the
> [Primary] key (PK) column corresponds to the PK in the parent table and
> therefore is solution was viable for you. Is that not correct? If so, then
> could you provide more details on your table structures and the real SQL
FTS[vbcol=seagreen]
> query?
> Thanks,
> John
>
> "Marcus" <lumbus@.ludd.luth.se> wrote in message
> news:uBT$PwdPEHA.4036@.TK2MSFTNGP12.phx.gbl...
other[vbcol=seagreen]
doesn't[vbcol=seagreen]
contain
> string
> something
>

FTS Multiple tables and columns. Different spin. Help!

Been reading thru all the posts and KB's on dealing with searching
across multiple columns. Most examples indicate you know what you're
looking for in each column.
Here's my big problem:
A: I have 12 tables, each with multiple columns that need to be
searched.
B: User will enter a search condition in any or all the boxes below
1 - "All these words"
2 - "Exact phrase"
3 - "Any of these words"
Unlike the SP3 "fix", if they enter "house cat" in "all these words", I
want a match if "house" and "cat" are in different tables.
In a nutshell, I need to take the 3 inputs, and search across all the
tables, and get ranked results.
The only answer I see, which seems to be kludge, is to create another
table with one column that concats all the indexable columns for all
the tables.
Doing a monster union with multiple contains/free/? on each select
seems really ugly and would kill performance.
Am I missing the boat, or does anyone have a better solution?
All comments are REALLY appreciated, as I just pulled out the last hair
on my head!
Thanks.
My approach would be to consolidate all of your data into a single table. I
realize it doesn't help much.
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
"woody" <shellymatthys@.yahoo.com> wrote in message
news:1116568453.936941.176620@.g47g2000cwa.googlegr oups.com...
> Been reading thru all the posts and KB's on dealing with searching
> across multiple columns. Most examples indicate you know what you're
> looking for in each column.
> Here's my big problem:
> A: I have 12 tables, each with multiple columns that need to be
> searched.
> B: User will enter a search condition in any or all the boxes below
> 1 - "All these words"
> 2 - "Exact phrase"
> 3 - "Any of these words"
> Unlike the SP3 "fix", if they enter "house cat" in "all these words", I
> want a match if "house" and "cat" are in different tables.
> In a nutshell, I need to take the 3 inputs, and search across all the
> tables, and get ranked results.
> The only answer I see, which seems to be kludge, is to create another
> table with one column that concats all the indexable columns for all
> the tables.
> Doing a monster union with multiple contains/free/? on each select
> seems really ugly and would kill performance.
> Am I missing the boat, or does anyone have a better solution?
> All comments are REALLY appreciated, as I just pulled out the last hair
> on my head!
> Thanks.
>