How can I use a containstable with a restriction on the data in the table it is searching?

Question

I have a table which contains my ads that can be searched in sql-server-2008. See the following piece of code. is_searchable in the ad_search table is basically the flag for whether this ad should be included in the search.

The three examples below produce unreasonable output in my mind:

In the first example 1000 records are returned. all with is_searchable set to 0. These rows are useless to me in this use case.

The second example shows the result of having a where clause filtering out is_searchable records that are not true. Because none were returned in the containstable in the from part of the query none were returned when the where clause was applied.

The third example only has the top_n_by_rank changed from 1000 to 4000.

So my question is how can I just get the TOP 1000 or n results with my "is_searchable =1" clause?

use live;

declare @search_text as varchar(2000) = '"*interlet*"'


--- TOP 1000 without clause (returns 1000 rows (all with is_searchable = 0)
select is_searchable, *
from CONTAINSTABLE (ad_search, searchText, @search_text, 1000) AS KEY_TBL
inner join ad_search (nolock) ON ad_search.ad_search_id = KEY_TBL.[KEY]
inner join ad_search_view_data on ad_search_view_data.ad_search_id = ad_search.ad_search_id


--- TOP 1000 with "where ad_search.is_searchable = 1" clause returns 0 rows
select is_searchable, *
from CONTAINSTABLE (ad_search, searchText, @search_text, 1000) AS KEY_TBL
inner join ad_search (nolock) ON ad_search.ad_search_id = KEY_TBL.[KEY]
inner join ad_search_view_data on ad_search_view_data.ad_search_id = ad_search.ad_search_id
where ad_search.is_searchable = 1

-- TOP 4000 
select is_searchable, *
from CONTAINSTABLE (ad_search, searchText, @search_text, 4000) AS KEY_TBL
inner join ad_search (nolock) ON ad_search.ad_search_id = KEY_TBL.[KEY]
inner join ad_search_view_data on ad_search_view_data.ad_search_id = ad_search.ad_search_id
where ad_search.is_searchable = 1 

Answer 1

Try this:

DECLARE @N int
SET @N=325

SELECT TOP(@N)
    is_searchable, *
    from CONTAINSTABLE (ad_search, searchText, @search_text) AS KEY_TBL
    inner join ad_search (nolock) ON ad_search.ad_search_id = KEY_TBL.[KEY]
    inner join ad_search_view_data on ad_search_view_data.ad_search_id = ad_search.ad_search_id
    where ad_search.is_searchable = 1
    ORDER BY KEY_TBL.Rank DESC

@N can be 1000 or whatever you want it to be