SQL Server Architecture for specific problem - full-text search - with full join

I am building an application that searches candidate's resumes. I need to use full-text search on the application as there are a lot of records and the resume field is fairly large. The issue is that for advanced searches, I have another table RelocationItems, that lists zips, states, etc. for the candidates relocation preferences and is related through a candidateID in the RelocationItems table. The problem is that sometimes a candidate will have no RelocationItems, sometimes they will have one, and sometimes they will have more than one. So, simple enough, I created a View that uses full outer join and then can select using DISTINCT on candidateID to find the candidates I need that will relocate to a certain area based on the search criteria.

The big problem with this view though as since it uses and Full Join, I can't use the full-text search now! (obviously so because my full-text index field is now not a unique not-null field)

And my stored procedure has the CONTAINS word in it so it won't even compile.

Should I : - Create a new table based on the view? (and then create another index identity field) - Do something to store the relocation items in the candidate table (maybe an XML field)? (I don't think you can store a table-value parameter in 2008 can you?) - Do some sort of Union of Tables (Queries)? (Run the search against the Candidates Table and then aga开发者_运维知识库inst the RelocationTable and then merge or union)?

Thanks for any suggestions on the best way to work around this problem!!!

---

I created a View that uses full outer join and then can select using DISTINCT on candidateID to find the candidates I need that will relocate to a certain area based on the search criteria.

Already a potential problem - a subselect with exists would be better.

A properly set up query would have no problem - do not use a join, go for a subselect and exists.