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
>
>