freetexttable query continued

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

geek-y-guy - 21 Feb 2007 19:02 GMT

Still working on this query:
select *
from products
inner join freetexttable(products,*,@searchstr) ft1
on ft1.[key] = products.p_id
left join sku
on sku.p_id= products.p_id
inner join freetexttable(sku,*,@searchstr) ft2
on ft2.[key] = sku.sku

I replaced the inner join with a left join on the second table.

I still get no results if the searchstr exists in the first table but not in
the second table. Full join doesn't work either.

Did I modify the wrong join? If I left-join on the FTcat I get all the rows
in both tables.

TIA!

Reply to this Message

Hilary Cotter - 22 Feb 2007 00:04 GMT

can you post the schemas of all related tables?

Signature

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

> Still working on this query:
> select *
[quoted text clipped - 15 lines]
>
> TIA!

Reply to this Message

geek-y-guy - 22 Feb 2007 13:01 GMT

> can you post the schemas of all related tables?

Products:
p_id varchar no 50 no no no SQL_Latin1_General_CP1_CI_AS
p_name varchar no 255 yes no yes SQL_Latin1_General_CP1_CI_AS
p_desc varchar no 1024 yes no yes SQL_Latin1_General_CP1_CI_AS
p_man int no 4 10 0 no (n/a) (n/a) NULL
row_id int no 4 10 0 no (n/a) (n/a) NULL
date_created datetime no 8 no (n/a) (n/a) NULL
date_modified datetime no 8 no (n/a) (n/a) NULL
(p_id is PK)

Manufacturers:
m_name varchar no 100 no no no SQL_Latin1_General_CP1_CI_AS
m_id int no 4 10 0 no (n/a) (n/a) NULL
(m_id is PK)

SKUs:
p_id varchar no 50 yes no yes SQL_Latin1_General_CP1_CI_AS
sku varchar no 50 no no no SQL_Latin1_General_CP1_CI_AS
sku_weight decimal no 9 18 1 yes (n/a) (n/a) NULL
attribute1 varchar no 50 yes no yes SQL_Latin1_General_CP1_CI_AS
attribute2 varchar no 50 yes no yes SQL_Latin1_General_CP1_CI_AS
status int no 4 10 0 yes (n/a) (n/a) NULL
wholesale_cost decimal no 5 7 2 no (n/a) (n/a) NULL
special_order bit no 1 no (n/a) (n/a) NULL
date_available datetime no 8 yes (n/a) (n/a) NULL
(sku is PK)

where
m_id in manufacturers = p_man in products
and p_id in sku = p_id in products

>> Still working on this query:
>> select *
[quoted text clipped - 15 lines]
>>
>> TIA!

Reply to this Message