Hi everyone,
I've enabled full-text search on my relational db. I have some problem
executing search between related tables. For example:

Master Table: Events

Events.ID (unique identifier for event)
Events.Location (string that contains the name of the city of the event)

Child Table: Event_Descriptions

Event_Descriptions.ID (KEY)
Event_Descriptions.LanguageID (KEY)
Event_Descriptions.Title (string that contains the title of the event)

The title is localized, that's why I need a child table. I succesfully index
the field "location" for the first table (events). The second table asks me a
unique field not null that i dont' have. Anyway I think that I can bypass the
problem adding an identity column to the table. Is right?

The most important thing (and the problem) is this: I wrote the following
query:

declare @query varchar(1024)
set @query = '"New York" AND "research"'

select KEY_TBL.[KEY] AS [IDEvento], KEY_TBL.RANK
from CONTAINSTABLE(EVENTS, *, @query, 100) AS KEY_TBL
order by KEY_TBL.RANK desc

I can specify only a table (in this case is events) for the containstable,
no joins are allowed. How can I find 'Research' in the 'Event_Descriptions'
table?

Thanks a lot,
Laura

you need to add a unique index to the event descriptions column. An identity
column will enforce uniqueness.

Near as I can figure this is what you are looking for:

create table events
(ID int not null identity(1,1) primary key,
location char(10))
GO
create table Event_Descriptions
(ID int not null foreign key references Events(ID),
LanguageID int,
Title varchar(2000))
GO
create unique index event_descriptions_unique on Event_descriptions(id)
go
exec sp_fulltext_database 'enable'
GO
exec sp_fulltext_catalog 'test', 'create'
GO
exec sp_fulltext_table 'Event_Descriptions', 'create', 'test',
'event_descriptions_unique'
GO
exec sp_fulltext_column 'Event_Descriptions', 'Title', 'add', 1033,null
GO
exec sp_fulltext_table 'Event_Descriptions', 'activate'
GO

select location, LanguageID ,Title from Events,
CONTAINSTABLE(EVENT_Descriptions, *, 'test', 100) AS KEY_TBL,
Event_Descriptions
where events.id=Key_Tbl.[key]
and events.id=Event_Descriptions.ID