i'm trying to usr two (or more) columns in the catalog in the select.
I can get all: FREETEXTTABLE(usr, * , @.term)
or 1 column: FREETEXTTABLE(usr, usrCompany , @.term)
but 2 or more: FREETEXTTABLE(usr, "usrCompany, usrBusDesc" , @.term)
doesn't work. I've seen in the book's on line that it can be done, but have
not found an example.
Any help appreciated! ...and Happy New Year!
thanks (as always)
some day i''m gona pay this forum back for all the help i''m getting
kes
Try
select * From FREETEXTTABLE(usr, (usrCompany, usrBusDesc) , @.term)
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
"WebBuilder451" <WebBuilder451@.discussions.microsoft.com> wrote in message
news:5CB29272-DA9E-484F-BECE-CD63610A6736@.microsoft.com...
> i'm trying to usr two (or more) columns in the catalog in the select.
> I can get all: FREETEXTTABLE(usr, * , @.term)
> or 1 column: FREETEXTTABLE(usr, usrCompany , @.term)
> but 2 or more: FREETEXTTABLE(usr, "usrCompany, usrBusDesc" , @.term)
> doesn't work. I've seen in the book's on line that it can be done, but
> have
> not found an example.
> Any help appreciated! ...and Happy New Year!
> --
> thanks (as always)
> some day i''m gona pay this forum back for all the help i''m getting
> kes
|||thank's for responding and happy new year,
I did try that, but i keep getting: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '('.
thanks (as always)
some day i''m gona pay this forum back for all the help i''m getting
kes
"Hilary Cotter" wrote:
> Try
> select * From FREETEXTTABLE(usr, (usrCompany, usrBusDesc) , @.term)
>
> --
> 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
> "WebBuilder451" <WebBuilder451@.discussions.microsoft.com> wrote in message
> news:5CB29272-DA9E-484F-BECE-CD63610A6736@.microsoft.com...
>
>
|||Is this SQL 2000? You can only do this in SQL 2005. In SQL 2000 its one
column or all columns (when you use a *)
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
"WebBuilder451" <WebBuilder451@.discussions.microsoft.com> wrote in message
news:2AF649A4-3375-4772-9DC9-E96FDC365309@.microsoft.com...[vbcol=seagreen]
> thank's for responding and happy new year,
> I did try that, but i keep getting: Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near '('.
> --
> thanks (as always)
> some day i''m gona pay this forum back for all the help i''m getting
> kes
>
> "Hilary Cotter" wrote:
|||it's 2000, that's the answer!
Thank You!
thanks (as always)
some day i''m gona pay this forum back for all the help i''m getting
kes
"Hilary Cotter" wrote:
> Is this SQL 2000? You can only do this in SQL 2005. In SQL 2000 its one
> column or all columns (when you use a *)
> --
> 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
>
> "WebBuilder451" <WebBuilder451@.discussions.microsoft.com> wrote in message
> news:2AF649A4-3375-4772-9DC9-E96FDC365309@.microsoft.com...
>
>
Showing posts with label freetexttable. Show all posts
Showing posts with label freetexttable. Show all posts
Thursday, March 8, 2012
another Freetexttable query problem
Hi All: Last month, I was looking for way to query 3 FT tables
simultaneously.
I found that a UNION with 3 separate queries worked, but someone pointed out
that duplicates would occur (and they did).
It was suggested that I use a query like:
select <columns>
from table1
inner join freetexttable(table1,*, @.srchstring) ft1
on ft1.key = table1.<key column>
inner join table2
on table2.<common key> = table1.<common key>
inner join freetexttable(table2,*, @.srchstring) ft2
on ft2.key = table2.<key column>
I did get that to work, but the problem is the query won't return any
matches if the searchstring is not found in both FT tables.
Going back to my original goal, I have 3 tables:
products
manufacturers
skus
And these tables are all in the FT cat.
I want to be able to search for a term like "blue" or "5180-1" (a sku) and
return a match from any table in the FT cat.
Is there any way to do it with the nested join query above? Or is there
another way to do it?
Before I started using the freetexttable query, I was using some fugly code
like:
SELECT <columns> FROM products WHERE <columns> LIKE '%<searchstr>%'
<if no results then>
SELECT <columns> FROM manufacturers WHERE <columns> LIKE '%<searchstr>%'
...do I need to do something like that, but instead concatenate the results
from each query into a temp table?
Thanks for any advice!
Hello geek-y-guy,
If you use a left join your query should work
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons
> Hi All: Last month, I was looking for way to query 3 FT tables
> simultaneously.
> I found that a UNION with 3 separate queries worked, but someone
> pointed out that duplicates would occur (and they did).
> It was suggested that I use a query like:
> select <columns>
> from table1
> inner join freetexttable(table1,*, @.srchstring) ft1
> on ft1.key = table1.<key column>
> inner join table2
> on table2.<common key> = table1.<common key>
> inner join freetexttable(table2,*,
> @.srchstring) ft2
> on ft2.key = table2.<key
> column>
> I did get that to work, but the problem is the query won't return any
> matches if the searchstring is not found in both FT tables.
> Going back to my original goal, I have 3 tables:
> products
> manufacturers
> skus
> And these tables are all in the FT cat.
> I want to be able to search for a term like "blue" or "5180-1" (a sku)
> and return a match from any table in the FT cat.
> Is there any way to do it with the nested join query above? Or is
> there another way to do it?
> Before I started using the freetexttable query, I was using some fugly
> code like:
> SELECT <columns> FROM products WHERE <columns> LIKE
> '%<searchstr>%'
> <if no results then>
> SELECT <columns> FROM manufacturers WHERE <columns> LIKE
> '%<searchstr>%'
> ...do I need to do something like that, but instead concatenate the
> results from each query into a temp table?
> Thanks for any advice!
>
|||Thanks Simon, but are you saying a left join for every join in the query?
for the 3 tables there would be 6 joins in total.
"Simon Sabin" <SimonSabin@.noemail.noemail> wrote in message
news:62959f1a4eb038c9236201101b77@.msnews.microsoft .com...
> Hello geek-y-guy,
> If you use a left join your query should work
>
> Simon Sabin
> SQL Server MVP
> http://sqlblogcasts.com/blogs/simons
>
>
simultaneously.
I found that a UNION with 3 separate queries worked, but someone pointed out
that duplicates would occur (and they did).
It was suggested that I use a query like:
select <columns>
from table1
inner join freetexttable(table1,*, @.srchstring) ft1
on ft1.key = table1.<key column>
inner join table2
on table2.<common key> = table1.<common key>
inner join freetexttable(table2,*, @.srchstring) ft2
on ft2.key = table2.<key column>
I did get that to work, but the problem is the query won't return any
matches if the searchstring is not found in both FT tables.
Going back to my original goal, I have 3 tables:
products
manufacturers
skus
And these tables are all in the FT cat.
I want to be able to search for a term like "blue" or "5180-1" (a sku) and
return a match from any table in the FT cat.
Is there any way to do it with the nested join query above? Or is there
another way to do it?
Before I started using the freetexttable query, I was using some fugly code
like:
SELECT <columns> FROM products WHERE <columns> LIKE '%<searchstr>%'
<if no results then>
SELECT <columns> FROM manufacturers WHERE <columns> LIKE '%<searchstr>%'
...do I need to do something like that, but instead concatenate the results
from each query into a temp table?
Thanks for any advice!
Hello geek-y-guy,
If you use a left join your query should work
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons
> Hi All: Last month, I was looking for way to query 3 FT tables
> simultaneously.
> I found that a UNION with 3 separate queries worked, but someone
> pointed out that duplicates would occur (and they did).
> It was suggested that I use a query like:
> select <columns>
> from table1
> inner join freetexttable(table1,*, @.srchstring) ft1
> on ft1.key = table1.<key column>
> inner join table2
> on table2.<common key> = table1.<common key>
> inner join freetexttable(table2,*,
> @.srchstring) ft2
> on ft2.key = table2.<key
> column>
> I did get that to work, but the problem is the query won't return any
> matches if the searchstring is not found in both FT tables.
> Going back to my original goal, I have 3 tables:
> products
> manufacturers
> skus
> And these tables are all in the FT cat.
> I want to be able to search for a term like "blue" or "5180-1" (a sku)
> and return a match from any table in the FT cat.
> Is there any way to do it with the nested join query above? Or is
> there another way to do it?
> Before I started using the freetexttable query, I was using some fugly
> code like:
> SELECT <columns> FROM products WHERE <columns> LIKE
> '%<searchstr>%'
> <if no results then>
> SELECT <columns> FROM manufacturers WHERE <columns> LIKE
> '%<searchstr>%'
> ...do I need to do something like that, but instead concatenate the
> results from each query into a temp table?
> Thanks for any advice!
>
|||Thanks Simon, but are you saying a left join for every join in the query?
for the 3 tables there would be 6 joins in total.
"Simon Sabin" <SimonSabin@.noemail.noemail> wrote in message
news:62959f1a4eb038c9236201101b77@.msnews.microsoft .com...
> Hello geek-y-guy,
> If you use a left join your query should work
>
> Simon Sabin
> SQL Server MVP
> http://sqlblogcasts.com/blogs/simons
>
>
Subscribe to:
Posts (Atom)