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