Hi,

I'm trying to use fulltextsearching with containstable and need to search in multiple tables. I have two catalogs set up and was wanting to merge the results. The below does not work because it says that People_Rank and Kwd_Rank don't exist (see bold in query)?

Does anyone have any ideas on how to better achieve this.


Code:
declare @SearchStr nvarchar(50)
set @SearchStr='protein or structure or function'

Select 
	(select Key_Tbl.Rank
	from people p2 
	inner join containstable(people,(blurb,Key_Phrases),@SearchStr) Key_Tbl on p2.User_Id=Key_Tbl.[Key]
	where p2.User_Id=p.User_Id) As People_Rank,
	(select Key_Tbl.Rank
	from person_keywords u
	inner join lu_keywords l on u.keyword_id=l.keyword_id
	inner join containstable(lu_keywords,(keyword_desc),@SearchStr) Key_Tbl on l.keyword_Id=Key_Tbl.[Key]
	where u.status='A'
	AND u.User_Id=p.User_Id) As Kwd_Rank,
	Sum(People_Rank+Kwd_Rank) As Total_Rank,
	p.user_id,
	p.User_Name
from people p
where p.deleted=0
order by total_rank desc, people_rank desc, kwd_rank desc
Cheers Al