Hello -

I have several different "content" types (articles, audios, videos, etc.)
that have a parent->child relationship structure in my SQL Server 2005 DB.
The parent table is called Content and all the common fields to these items
are contained within it (e.g. title, name, description). The child tables
are called articles, audios, videos, etc., and they contain fields unique to
each type. For example, they all contain a field called body but only audios
contains a field called URL. The content table contains a unique primary key
called ContentID and all child tables are related using this field.

I would like to create a "search engine" that can search all of these tables
and return the rank of content items regardless of which "type" of content it
is. For example, a search should look at the Content.[Title],
Content.[Name], Content.[Description], Audio.[Body], Articles.[Body],
Videos.[Body], etc.

I can create the propery Full-Text index but can not forumulate the proper
T-SQL to return a list of unique ContentIDs and their ranks of the search.

I would have to imagine this is somewhat of a common issue but can't seem to
find any information related to solving it.