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