Counting

Consider the following:
Table setup:
ID Message-ID References
1 IDA
2 IDB IDA
3 IDC IDA IDB
4 IDD
Given the above table I am trying to select the ID, Message-ID along with
the References count. So the following will work and get the desired
results its just slow. I have already indexed References for full text
searching so I would like to utilize that for speed. However I cannot
figure out how to run a count using the full text indexing for speed.
SELECT [ID], [Message-ID], (SELECT Count(ID) FROM tblArticles WHERE
[References] LIKE '%' + a.[Message-ID] + '%') AS ReferenceCount FROM
tblArticles a
Hi John,
Sorry for the late reply... I got side-track on other issues. While the
T-SQL LIKE works, a similarly constructed query for SQL FTS CONTAINS will
never work. Previously, you had posted this query as an example:
SELECT [ID], [References], [From], [Date], [Subject], (SELECT Count(ID) FROM
tblarticles WHERE CONTAINS ([References], a.[Message-ID])) AS ReferenceCount
FROM tblarticles a WHERE [GroupID] = @.GroupID AND [References]='' ORDER BY
[Date] Desc;
While the BOL is not specific on this issue (it is a DOC bug, IMHO), the
CONTAINS syntax is as follows:
CONTAINS
( { column | * } , '< contains_search_condition >'
)
Specifically, the contains_search_condition cannot be an aliased column as
in your above example - "a.[Message-ID]". However, what may work for you is
to split the query into two parts and use either a cursor or temp table to
store an intermediate results, for example:
set nocount on
DECLARE keyword_cursor CURSOR FAST_FORWARD
FOR
select kword from keyword
CREATE TABLE #authors_PK (author_pk char(11))
OPEN keyword_cursor
DECLARE @.keyword varchar(50), @.author_pks char(11), @.sql nvarchar(600)
-- Fetch the first row in the cursor.
FETCH NEXT FROM keyword_cursor INTO @.keyword
WHILE @.@.FETCH_STATUS = 0
BEGIN
select @.sql = 'insert into #authors_PK (author_pk) select au_id from
authors where contains(*, ''' + @.keyword + ''')'
exec(@.sql)
FETCH NEXT FROM keyword_cursor INTO @.keyword
END
CLOSE keyword_cursor
DEALLOCATE keyword_cursor
select * from #authors_PK
drop table #authors_PK
You should modifiy the above query's DECLARE CURSOR FOR to "SELECT [ID],
[Message-ID] FROM tblArticles" and then inside the WHILE loop run the
"SELECT Count(ID) FROM tblarticles WHERE CONTAINS ([References],
@.reference_variable)" query.
Regards,
John
"John Doe" <uce@.ftc.gov> wrote in message
news:joBmc.16541$jU.962253@.twister.southeast.rr.co m...
> Consider the following:
> Table setup:
> ID Message-ID References
> 1 IDA
> 2 IDB IDA
> 3 IDC IDA IDB
> 4 IDD
>
> Given the above table I am trying to select the ID, Message-ID along with
> the References count. So the following will work and get the desired
> results its just slow. I have already indexed References for full text
> searching so I would like to utilize that for speed. However I cannot
> figure out how to run a count using the full text indexing for speed.
> SELECT [ID], [Message-ID], (SELECT Count(ID) FROM tblArticles WHERE
> [References] LIKE '%' + a.[Message-ID] + '%') AS ReferenceCount FROM
> tblArticles a
>
>