FreeTextTable linked to multiple joins

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,
It's un-clear to me from the information that you've provided below whether
or not "category", "SubCategory" are separate tables or are columns in the
same table. Could you provide more specific information? Specifically, the
output of sp_help <table_name(s)> ?
If what you are looking for is how to search multiple columns in one table
using FREETEXTTABLE, please review this T-SQL Query example using the
Northwind database table employees:
use Northwind
go
SELECT e.LastName, e.FirstName, e.Title, e.Notes
from Employees AS e,
freetexttable(Employees, Notes, 'BA') as A,
freetexttable(Employees, Title, 'Sales') as B
where
A.[KEY] = e.EmployeeID and
B.[KEY] = e.EmployeeID
Note, that the same table (Employees) is use with multiple columns (Notes
and Title) from this table.
Hopefully, this is what you're looking for.
Regards,
John
"Chris Kennedy" <nospam@.nospam.co.uk> wrote in message
news:u4LHWoNHEHA.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.
>
|||No I was looking for searches across multiple tables.
"John Kane" <jt-kane@.comcast.net> wrote in message
news:ut7VbWZHEHA.3128@.TK2MSFTNGP12.phx.gbl...
> Chris,
> It's un-clear to me from the information that you've provided below
whether
> or not "category", "SubCategory" are separate tables or are columns in the
> same table. Could you provide more specific information? Specifically, the
> output of sp_help <table_name(s)> ?
> If what you are looking for is how to search multiple columns in one table
> using FREETEXTTABLE, please review this T-SQL Query example using the
> Northwind database table employees:
> use Northwind
> go
> SELECT e.LastName, e.FirstName, e.Title, e.Notes
> from Employees AS e,
> freetexttable(Employees, Notes, 'BA') as A,
> freetexttable(Employees, Title, 'Sales') as B
> where
> A.[KEY] = e.EmployeeID and
> B.[KEY] = e.EmployeeID
> Note, that the same table (Employees) is use with multiple columns (Notes
> and Title) from this table.
> Hopefully, this is what you're looking for.
> Regards,
> John
>
>
> "Chris Kennedy" <nospam@.nospam.co.uk> wrote in message
> news:u4LHWoNHEHA.3952@.TK2MSFTNGP10.phx.gbl...
> the
> Sub
> return
> have
>
|||Chris,
Ok, try this...
SELECT e.LastName, e.FirstName, e.Title, e.Notes t.TerritoryID
from Employees AS e, EmployeeTerritories t,
containstable(Employees, Notes, 'BA') as A,
containstable(EmployeeTerritories, TerritoryID, 'Sales') as B
where
A.[KEY] = e.EmployeeID and
B.[KEY] = e.EmployeeID
Note, in order for this example to work, you must alter the table
EmployeeTerritories and add a single non-null column in order to use as the
FT-Index key.
Regards,
John
"Chris Kennedy" <chrisknospam@.cybase.co.uk> wrote in message
news:Ok5aIorIEHA.964@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
> No I was looking for searches across multiple tables.
> "John Kane" <jt-kane@.comcast.net> wrote in message
> news:ut7VbWZHEHA.3128@.TK2MSFTNGP12.phx.gbl...
> whether
the[vbcol=seagreen]
the[vbcol=seagreen]
table[vbcol=seagreen]
(Notes[vbcol=seagreen]
as[vbcol=seagreen]
and[vbcol=seagreen]
category[vbcol=seagreen]
I
>