Column in contains clause?

Dear all,
Another question: I would like to get an overview of how often a number
of words, stored in a table KO, occur in a full text indexed column on
another table. I can do this seperately:
select * from names
id | name
--+--
1 | bush
2 | kerry
select count(*) from texts where contains(text, 'bush')
2,123
select count(*) from texts where contains(text, 'dean')
1,326
[numbers entiry fictional]
But since I have a large number of such names, I would like to just
join the count per name to the names table like so:
select name, count(*) from
names n, texts t
where contains(text, n.name)
group by name
but this returns an 'incorrect syntax near n'.
Why doesn't the above work? Is what I am trying to do possible using
the contains function? Is there another way to achieve this goal?
Thanks!
Wouter
It doesn't work because the Contains operator is expecting a single value
instead of a column - which is what you are passing.
I think what you need to do is something like this you might want to write a
cursor or perhaps a function that will return a table variable to accomplish
this.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"wouter" <wouter@.2at.nl> wrote in message
news:1108297074.753022.57670@.l41g2000cwc.googlegro ups.com...
> Dear all,
> Another question: I would like to get an overview of how often a number
> of words, stored in a table KO, occur in a full text indexed column on
> another table. I can do this seperately:
> select * from names
> id | name
> --+--
> 1 | bush
> 2 | kerry
> select count(*) from texts where contains(text, 'bush')
> 2,123
> select count(*) from texts where contains(text, 'dean')
> 1,326
> [numbers entiry fictional]
> But since I have a large number of such names, I would like to just
> join the count per name to the names table like so:
> select name, count(*) from
> names n, texts t
> where contains(text, n.name)
> group by name
> but this returns an 'incorrect syntax near n'.
> Why doesn't the above work? Is what I am trying to do possible using
> the contains function? Is there another way to achieve this goal?
> Thanks!
> Wouter
>