Nested Full Text Queries

Can someone help me or provide a workaround? The object is to select all
articles which are not references to other articles. The problem I am
having is when I try to get the ReferenceCount (which is the count of
articles which reference this article) it is throwing a 42000 (Error 170:
Line 6: Incorrect syntax near 'a'.). It seems as though I cannot have a
nested full text query. I have tried successfully using LIKE '%' +
a.[Message-ID] + '%' which returns correctly just slow. By the way the The
References field is a varchar(500).
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;
John,
I'm not exactly sure what you're trying to achieve with this query, but it
is the wrong approach for SQL Server 2000 (?) Full-text Search. I *think*
what you may want to do is an INNER JOIN between the FTS query using
CONTAINSTABLE and your table tblarticles. For example, the syntax of the
rowset-based CONTAINSTABLE is:
SELECT select_list
FROM table AS FT_TBL INNER JOIN
CONTAINSTABLE(table, column, contains_search_condition) AS KEY_TBL
ON FT_TBL.unique_key_column = KEY_TBL.[KEY]
-- and an example using a Pubs database table:
SELECT FT_TBL.au_lname, FT_TBL.au_fname, KEY_TBL.RANK
FROM authors as FT_TBL,
CONTAINSTABLE (authors,au_lname, '("ring" or "ringer") or ("green" or
"greene")' ) AS KEY_TBL
WHERE
FT_TBL.au_id = KEY_TBL.[KEY]
/*-- returns:
au_lname au_fname RANK
-- -- --
Greene Morningstar 80
Green Marjorie 80
Ringer Anne 64
Ringer Albert 64
(4 row(s) affected)
*/
I'd recommend altering your query to use the above CONTAINSTABLE or
FREETEXTTABLE syntax.
Regards,
John
"John Doe" <uce@.ftc.gov> wrote in message
news:oU_gc.23991$6m4.934106@.twister.southeast.rr.c om...
> Can someone help me or provide a workaround? The object is to select all
> articles which are not references to other articles. The problem I am
> having is when I try to get the ReferenceCount (which is the count of
> articles which reference this article) it is throwing a 42000 (Error 170:
> Line 6: Incorrect syntax near 'a'.). It seems as though I cannot have a
> nested full text query. I have tried successfully using LIKE '%' +
> a.[Message-ID] + '%' which returns correctly just slow. By the way the
The
> References field is a varchar(500).
> 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;
>
>
|||Thanks for replying john,
Unless I am overlooking something and please correct me if I am, you simply
provided an example of how to do a standard full text search. I do not have
a problem doing that. I thought I explained what im trying to achieve very
well.
Again, This is what im trying to do but its to slow:
SELECT [ID], [References], [From], [Date], [Subject], (SELECT Count(ID) FROM
tblarticles WHERE [References] LIKE '%' + a.[Message-ID] + '%') AS
ReferenceCount
FROM tblarticles a WHERE [GroupID] = @.GroupID AND [References]='' ORDER BY
[Date] Desc;
This is what I have tried:
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;
Notice that I am trying to substitute the "LIKE '%' + a.[Message-ID] + '%' "
for "CONTAINS([References], a.[Message-ID])"
"John Kane" <jt-kane@.comcast.net> wrote in message
news:upgfjBpJEHA.752@.tk2msftngp13.phx.gbl...
> John,
> I'm not exactly sure what you're trying to achieve with this query, but it
> is the wrong approach for SQL Server 2000 (?) Full-text Search. I *think*
> what you may want to do is an INNER JOIN between the FTS query using
> CONTAINSTABLE and your table tblarticles. For example, the syntax of the
> rowset-based CONTAINSTABLE is:
> SELECT select_list
> FROM table AS FT_TBL INNER JOIN
> CONTAINSTABLE(table, column, contains_search_condition) AS KEY_TBL
> ON FT_TBL.unique_key_column = KEY_TBL.[KEY]
> -- and an example using a Pubs database table:
> SELECT FT_TBL.au_lname, FT_TBL.au_fname, KEY_TBL.RANK
> FROM authors as FT_TBL,
> CONTAINSTABLE (authors,au_lname, '("ring" or "ringer") or ("green" or
> "greene")' ) AS KEY_TBL
> WHERE
> FT_TBL.au_id = KEY_TBL.[KEY]
> /*-- returns:
> au_lname au_fname RANK
> -- -- --
> Greene Morningstar 80
> Green Marjorie 80
> Ringer Anne 64
> Ringer Albert 64
> (4 row(s) affected)
> */
> I'd recommend altering your query to use the above CONTAINSTABLE or
> FREETEXTTABLE syntax.
> Regards,
> John
|||You're welcome, John,
I've given this some more thought and have done some testing with the
authors table in the Pubs database I do not have the table schema for your
tblarticles table. First of all, the syntax you are using for the CONTAINS
predicate is incorrect, and assuming you are referencing a variable in the
search clause, your query should be re-written as follows:
declare @.SearchStr varchar(8000)
SET @.SearchStr = '"green" or "white"'
SELECT [ID], [References], [From], [Date], [Subject],
(SELECT Count(ID) FROM tblarticles WHERE CONTAINS ([References],
@.SearchStr)) AS ReferenceCount
FROM tblarticles a
WHERE [GroupID] = @.GroupID AND [References]=''
ORDER BY [Date] Desc;
Additionally, I tested the following alternative using CONTAINSTABLE with
and INNER JOIN on the a SELECT * from CONTAINSTABLE. However, this solution
does not work with count(*) or count(au_id) for the join condition.
declare @.SearchStr varchar(8000)
SET @.SearchStr = '"green" or "white"'
SELECT FT_TBL.au_lname, FT_TBL.au_fname, KEY_TBL.RANK
FROM authors as FT_TBL INNER JOIN
(SELECT * FROM
CONTAINSTABLE(authors,au_lname, @.SearchStr)) AS KEY_TBL
ON FT_TBL.au_id = KEY_TBL.[KEY]
/* -- returns:
au_lname au_fname RANK
--- -- --
Green Marjorie 80
White Johnson 80
(2 row(s) affected)
*/
Finally, I altered the initial query and use the assignment symbol "=" for
the SELECT * from authors where contains() and this did the trick.
declare @.SearchStr varchar(8000), @.State char(2)
SET @.SearchStr = '"green" or "white"'
SET @.State = 'CA'
SELECT au_id, au_lname, au_fname, SearchCount = (SELECT count(*) FROM
authors where CONTAINS(au_lname, @.SearchStr))
FROM authors
WHERE state = @.State and contract = 0
ORDER BY au_lname DESC
/*-- returns:
au_id au_lname au_fname
SearchCount
-- --- -- --
724-08-9931 Stringer Dirk 2
893-72-1158 McBadden Heather 2
(2 row(s) affected)
*/
You should alter your query to match the above and confirm that this works
for your tblarticles table.
Regards,
John
"John Doe" <uce@.ftc.gov> wrote in message
news:qu8hc.44033$yv.952732@.twister.southeast.rr.co m...
> Thanks for replying john,
> Unless I am overlooking something and please correct me if I am, you
simply
> provided an example of how to do a standard full text search. I do not
have
> a problem doing that. I thought I explained what im trying to achieve
very
> well.
> Again, This is what im trying to do but its to slow:
> SELECT [ID], [References], [From], [Date], [Subject], (SELECT Count(ID)
FROM
> tblarticles WHERE [References] LIKE '%' + a.[Message-ID] + '%') AS
> ReferenceCount
> FROM tblarticles a WHERE [GroupID] = @.GroupID AND [References]='' ORDER BY
> [Date] Desc;
> This is what I have tried:
> 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;
> Notice that I am trying to substitute the "LIKE '%' + a.[Message-ID] + '%'
"[vbcol=seagreen]
> for "CONTAINS([References], a.[Message-ID])"
> "John Kane" <jt-kane@.comcast.net> wrote in message
> news:upgfjBpJEHA.752@.tk2msftngp13.phx.gbl...
it[vbcol=seagreen]
*think*
>
|||Again thanks John,
The problem I am having is that where you are using a predefined (or passed)
variable @.SearchStr, I am needing this to come from the outer query. This
is why you see my variable labeled as a.[Message-ID]. The "a" is a
reference to the outer SELECT statements [Message-ID] field.
My Table looks like this:
CREATE TABLE [dbo].[tblArticles] (
[ID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
[FROM] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[DATE] [datetime] NOT NULL ,
[Subject] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Message-ID] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[Path] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Date Entered] [datetime] NOT NULL ,
[Article] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[References] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[GroupID] [numeric](18, 0) NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
And for the record this gets the desired results, its just slow and does not
make use of full text.
SELECT [ID], [References], [From], [Date], [Subject], (SELECT Count(ID) FROM
tblarticles WHERE [References] LIKE '%' + a.[Message-ID] + '%') AS
ReferenceCount FROM tblarticles a WHERE [GroupID] = @.GroupID AND
[References]='' ORDER BY [Date] Desc;
Which again brings me back to the original question of nesting full text
queries.
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;

> --SNIP --
>