freetextable and multiple tables

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

shank - 12 Jul 2004 00:55 GMT

In the below FREETEXTTABLE query, I have the SELECT fields indexed in the FT
catalog. I should be getting results and I'm not. The problem, as I see it,
is that I need to be searching all 3 tables when actually I'm only searching
1 table in this expression: FREETEXTTABLE(ItemStock, *, @SearchCriteria). At
the very bottom, I removed 2 tables and the query works. How do I search all
3 tables and get results?
thanks

DECLARE @SearchCriteria varchar(100)
SET @SearchCriteria = ' "midler*" '
SELECT
ItemStock.OrderNo,
ItemStock.Description,
ItemStock.Category,
ItemStock.s_Type,
ItemStock.Manuf,
ItemStock.Label,
ItemTitles.Title,
ItemTitles.Artist,
ItemHardware.m_Specs,
ItemStock.ManCode
FROM
ItemStock
INNER JOIN ItemTitles ON ItemStock.OrderNo = ItemTitles.OrderNo
INNER JOIN ItemHardware ON ItemStock.OrderNo =
ItemHardware.OrderNo
INNER JOIN FREETEXTTABLE(ItemStock, *, @SearchCriteria) AS
FS_TABLE ON FS_TABLE.[KEY] = ItemStock.OrderNo
WHERE
FS_TABLE.RANK > 0
ORDER BY
FS_TABLE.Rank DESC
========================================
--This one works....
DECLARE @SearchCriteria varchar(100)
SET @SearchCriteria = ' "midler*" '
SELECT
ItemStock.OrderNo,
ItemStock.Description,
ItemStock.Category,
ItemStock.s_Type,
ItemStock.Manuf,
ItemStock.Label,
--ItemTitles.Title,
--ItemTitles.Artist,
--ItemHardware.m_Specs,
ItemStock.ManCode
FROM
ItemStock
INNER JOIN FREETEXTTABLE(ItemStock, *, @SearchCriteria) AS
FS_TABLE ON FS_TABLE.[KEY] = ItemStock.OrderNo
WHERE
FS_TABLE.RANK > 0
ORDER BY
FS_TABLE.Rank DESC

Reply to this Message

Hilary Cotter - 13 Jul 2004 01:44 GMT

you can't query multiple tables in a single full text query unless there is
some sort of a parent child relationship between ItemStock.OrderNo and
ItemHardware, and ItemTitles.

Here is an example of such a parent child relationship:

CREATE TABLE [dbo].[Parent] (
[pk] [int] IDENTITY (1, 1) NOT NULL ,
[Title] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Author] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[vpath] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[characterization] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[Size] [int] NULL ,
[CreateDate] [datetime] NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[TextTable] (
[pk] [int] IDENTITY (1, 1) NOT NULL ,
[textcol] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

I am storing the text data in the child table and both the parent and the
child share a common pk. There is no DRI constraint.

This makes the ContainsTable query a little more complex. Here is my stored
procedure that I use to query this table.

CREATE PROC search @search nvarchar(20), @@strSearch nvarchar(500)

AS

SET @@strSearch='SELECT Title, author, vpath, characterization, size,
createDate from parent as P,_ CONTAINSTABLE(TextTable, TextCol,
'+char(39)+char(34)

SELECT @@strSearch=@@strSearch + @search +char(34)+char(39)

SELECT @@strSearch=@@strSearch+ ', 100) AS FTTABLE WHERE P.pk=
FTTABLE.[key]'

SELECT @@strSearch= @@strSearch + ' AND size > 10000 ORDER BY FTTABLE.rank
desc,[KEY] '

exec (@@strSearch)

usage is:

EXEC search 'microsoft sql server',''

Notice that what we are doing is returning the results set from MSSearch as
a derived table and joining this derived table (named FTTABLE) against the
parent table. The child table which contains our text information is not
queried as all. Also notice that I am not doing any error checking or
returning any return codes from this stored procedure. My experience is that
building the search string can be made error free, and the errors generated
by MSSearch will be handled by the calling application and in some cases
will not be trappable with the @@error system variable.

Signature

Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html

> In the below FREETEXTTABLE query, I have the SELECT fields indexed in the FT
> catalog. I should be getting results and I'm not. The problem, as I see it,
[quoted text clipped - 51 lines]
> ORDER BY
> FS_TABLE.Rank DESC

Reply to this Message

John Kane - 12 Jul 2004 06:22 GMT

Hi Hilary,
While I respectively disagree that a "parent child relationship" must exist
to use multiple FT-enabled tables with FREETEXTTABLE or CONTAINSTABLE
query, I suspect that what you really meant was that a Primary key / Foreign
key relationship must exist between the tables to allow a join. It's a small
point, but not all PK / FK relationships have to be "parent child"
relationships. For example take the two tables Employees and
EmployeeTerritories in the Northwind database, while they are not in a
classic parent/child relationship, they do in fact share a PK/FK
relationship on their respective EmployeeID columns, specifically:

use Northwind
go
exec sp_help EmployeeTerritories -- PK is PK_EmployeeTerritories
(EmployeeID, TerritoryID)
exec sp_help Employees -- PK is PK_Employees (EmployeeID)

select * from EmployeeTerritories where EmployeeID = 1
/* returns:
EmployeeID TerritoryID
----------- --------------------
1 06897
1 19713
*/

select EmployeeID, LastName, FirstName, Title, Address from Employees where
EmployeeID = 1
/* returns:
EmployeeID LastName FirstName Title
Address
----------- -------------------- ---------- ------------------------------ -
-------------------
1 Davolio Nancy Sales Representative
507 - 20th Ave. E.
*/

In order for the EmployeeTerritories to be FT Indexed, the table must be
altered to add a single column, non-nullable key and non-clustered, unique
index, for example:

ALTER TABLE EmployeeTerritories ADD ET_Ident int identity (1, 1) NOT NULL
go -- returns: 49 rows affected.
select * from EmployeeTerritories where EmployeeID = 1
/* returns:
EmployeeID TerritoryID ET_Ident
----------- -------------------- -----------
1 06897 1
1 19713 2
*/
CREATE UNIQUE INDEX ET_Ident_IDX on EmployeeTerritories(ET_Ident)
go

Then you can create a FT Catalog and FT-enable both tables - Employees and
EmployeeTerritories - and then run a Full Population on both tables and you
can execute a multiple FT-enabled FTS query, such as the one below:

SELECT distinct e.LastName, e.FirstName from Employees AS e,
EmployeeTerritories t,
containstable(Employees, FirstName, 'Nancy') as A,
containstable(EmployeeTerritories, TerritoryID, '06897') as B
where
A.[KEY] = e.EmployeeID and
B.[KEY] = e.EmployeeID
/* -- returns:
LastName FirstName
-------------------- ----------
Davolio Nancy
(1 row(s) affected)
*/

Shank, you should now be able to take the above example code and alter it to
fit your tables and selection criteria.
Regards,
John

> you can't query multiple tables in a single full text query unless there is
> some sort of a parent child relationship between ItemStock.OrderNo and
[quoted text clipped - 118 lines]
> > ORDER BY
> > FS_TABLE.Rank DESC

Reply to this Message

shank - 13 Jul 2004 00:01 GMT

Not sure if this going to help or not. Below are my 3 tables. I have a PK/FK
relationship between ItemStock.OrderNo = ItemTitles.OrderNo and also
ItemStock.OrderNo = ItemHardware.OrderNo. After setting up the indexes, I
rebuilt the catalog and still not getting results. Any further help would be
appreciated. This is my query...

DECLARE @SearchCriteria varchar(100)
SET @SearchCriteria = ' "midler*" '
SELECT
ItemStock.OrderNo,
ItemStock.Description,
ItemStock.Category,
ItemStock.s_Type,
ItemStock.Manuf,
ItemStock.Label,
ItemTitles.Title,
ItemTitles.Artist,
ItemHardware.m_Specs,
ItemStock.ManCode
FROM
ItemStock
INNER JOIN ItemTitles ON ItemStock.OrderNo = ItemTitles.OrderNo
INNER JOIN ItemHardware ON ItemStock.OrderNo =
ItemHardware.OrderNo
INNER JOIN FREETEXTTABLE(ItemStock, *, @SearchCriteria) AS
FS_TABLE ON FS_TABLE.[KEY] = ItemStock.OrderNo
WHERE
FS_TABLE.RANK > 0
ORDER BY
FS_TABLE.Rank DESC
---------------------------------------------
Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation Developer Edition on Windows
NT 5.1 (Build 2600: Service Pack 1)
---------------------------------------------
CREATE TABLE [dbo].[ItemStock] (
[OrderNo] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Label] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[SoftHard] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ManCode] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Description] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[d_Update] [datetime] NULL ,
[Exclusive] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Affiliate] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[NewRelease] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Status] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Inv] [decimal](5, 0) NULL ,
[d_Date] [datetime] NULL ,
[Category] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SingleArtist] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[s_Type] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TypeDescrip] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Cased] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Packed] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Manuf] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ManSort] [int] NULL ,
[Weight] [decimal](5, 2) NULL ,
[Icons75] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Icons100] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Icons200] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Icons300] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ID] [numeric](10, 0) IDENTITY (1, 1) NOT NULL
) ON [PRIMARY]
GO
---------------------------------------------
CREATE TABLE [dbo].[ItemTitles] (
[OrderNo] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Title] [varchar] (70) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Artist] [varchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Location] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[SortKey] [decimal](5, 0) NOT NULL ,
[Disc_ID] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MP3Files] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ID] [numeric](10, 0) IDENTITY (1, 1) NOT NULL
) ON [PRIMARY]
GO
---------------------------------------------
CREATE TABLE [dbo].[ItemHardware] (
[OrderNo] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[s_page] [varchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[s_Thumb] [varchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[s_pic] [varchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PDFScale] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[m_Specs] [varchar] (8000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PDFSpecs] [varchar] (8000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Notes] [varchar] (8000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Media] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Cass] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CDG] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[VCD] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DVD] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ID] [numeric](10, 0) IDENTITY (1, 1) NOT NULL
) ON [PRIMARY]
GO

Reply to this Message