CODEWITHSUNDEEP
sqljoinfull-text-search
Dale Marshall
Dale Marshall

Reputation: 1135

Trouble with Full Text Search query

I'm having trouble with this full text search query I'm trying to run. I need to do a full text search on two tables. If any of the terms are in either table I need to return the records from the first table.

select R.* from Request R 
    inner join Patients P on R.PatientID = P.PatientID
    inner join containstable(Request,(*),@keywords)AS KEY_TBL 
    ON R.RequestID = KEY_TBL.[Key] 
    full outer join 
    (select R.* from Request R 
    inner join Patients P on R.PatientID = P.PatientID
    inner join containstable(Patients,(*),@keywords) AS KEY_TBL2 
    ON P.PatientID = KEY_TBL2.[Key]) as b on R.RequestID = b.RequestID

Upvotes: 0

Views: 56

Answers (1)

Dale Marshall
Dale Marshall

Reputation: 1135

All I needed was a Union instead of a full outer join.

select R.* from Request R 
    inner join Patients P on R.PatientID = P.PatientID
    inner join containstable(Request,(*),@keywords)AS KEY_TBL 
    ON R.RequestID = KEY_TBL.[Key] 
    UNION
    select R.* from Request R 
    inner join Patients P on R.PatientID = P.PatientID
    inner join containstable(Patients,(*),@keywords) AS KEY_TBL2 
    ON P.PatientID = KEY_TBL2.[Key]

Upvotes: 1

Related Questions