[RESOLVED] SQL Server '05: containstable

[aconybeare]

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.

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

[aconybeare]

I've managed to do this with the following -

Declare @SearchStr nvarchar(50)
Set @SearchStr='protein or structure or function'

SELECT distinct
  COALESCE(People_Rank.Rank,0) + COALESCE(Keyword_Rank.Rank,0) AS Total_Rank,
  p.User_Id,
  p.User_Name
FROM People p
  LEFT OUTER JOIN Person_keywords k ON p.user_id=k.user_id
  LEFT OUTER JOIN LU_keywords l ON l.Keyword_id=k.Keyword_id
  LEFT OUTER JOIN CONTAINSTABLE(people,(blurb,Key_Phrases),@SearchStr) AS People_Rank ON p.User_Id=People_Rank.
  LEFT OUTER JOIN CONTAINSTABLE(lu_keywords,(keyword_desc),@SearchStr) AS Keyword_Rank ON k.Person_Kwd_ID=Keyword_Rank.
WHERE p.deleted=0
  AND (People_Rank.Rank IS NOT NULL OR Keyword_Rank.Rank IS NOT NULL)
  AND (k.Keyword_Type='KWD' OR k.Keyword_Type IS NULL)
  AND (k.Status='A' OR k.Status IS NULL)
  AND (l.Jnl_Code='bj' OR l.Jnl_Code IS NULL)
ORDER BY Total_Rank DESC

Cheers Al