Query Help: JOIN ON CONTAINS with FORMSOF

[Jeffrey A. Hawkins]

Hi,

I have been able to JOIN ON CONTAINS(column, 'whatever'), but can't seem to
get the syntax right to join on one field containing a related field.

The query below is what I am working with... As written, everything inside
the single quotes is interpreted as a literal string. I.e. "FormsOf" doesn't
act as a function, but just a bunch of letters. Any ideas?

Thanks!

SELECT 'Dolch' AS[List Name], dbo.Dolch.vchWord
FROM dbo.Dolch LEFT OUTER JOIN
dbo.CombinedLexicons ON
CONTAINS(dbo.Dolch.vchWord, 'FORMSOF(INFLECTIONAL,
dbo.CombinedLexicons.vchWord)')
WHERE (dbo.CombinedLexicons.vchWord IS NULL)

[Erland Sommarskog]

Jeffrey A. Hawkins (Sp******@Sorry.ALL) writes:

I have been able to JOIN ON CONTAINS(column, 'whatever'), but can't seem
to get the syntax right to join on one field containing a related field.

The query below is what I am working with... As written, everything
inside the single quotes is interpreted as a literal string. I.e.
"FormsOf" doesn't act as a function, but just a bunch of letters. Any
ideas?

Thanks!

SELECT 'Dolch' AS[List Name], dbo.Dolch.vchWord
FROM dbo.Dolch LEFT OUTER JOIN
dbo.CombinedLexicons ON
CONTAINS(dbo.Dolch.vchWord, 'FORMSOF(INFLECTIONAL,
dbo.CombinedLexicons.vchWord)')
WHERE (dbo.CombinedLexicons.vchWord IS NULL)

I don't use full-text myself, so you may get a better answer in
microsoft.public.sqlserver.fulltext. I played with:

SELECT 'Dolch' AS[List Name], dbo.Dolch.vchWord
FROM dbo.Dolch LEFT OUTER JOIN
dbo.CombinedLexicons ON
CONTAINS(dbo.Dolch.vchWord, 'FORMSOF(INFLECTIONAL, ' +
dbo.CombinedLexicons.vchWord + ')')
WHERE (dbo.CombinedLexicons.vchWord IS NULL)

But this gives me a syntax error.

So that, and what I can read out from Books Online about CONTAINS is
that you cannot use CONTAINS for a join. Since fulltext is an engine
external to SQL Server, it kind of make sense. The workaround would be
ro tun a cursor.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp