Simple header/child one-to-many table structure. I want to return the header ID when doing a FT search on a column in the child table.
But, if I include a NOT in the search statement I'm having problems where the child records belongs to a particular header that can contain both the valid part of the search and the NOT part. For example, if the searched column in the child consists of car model names, I might search for "Focus AND NOT Ka". This would bring me back the correct header for FORD focus. However, in the eyes of the users, the header record represents an invalid search result as it includes the child record "KA". NB : This is because the child table is really a vertical representation of a record. The query I've been using is:
SELECT dbo.iHeader.headerText, dbo.iHeader.headerId
FROM dbo.iChild FT_TBL INNER JOIN
CONTAINSTABLE(iChild, childText, '(Focus and not KA)') KEY_TBL ON FT_TBL.childId = KEY_TBL.[KEY] INNER JOIN
dbo.iHeader ON FT_TBL.headerId = dbo.iHeader.headerId
I hope I've explained this properly. I've been working in Exchange Public Folders and Domino land for quite some time and haven't done too much relational stuff!