Showing posts with label fields. Show all posts
Showing posts with label fields. Show all posts

Monday, March 26, 2012

Frustration with UniqueIdentifiers (GUIDs)

We're trying to set a new standard at my office, of using GUIDs as database ID fields - all new tables in all databases will have a GUID as the ID field, using the UniqueIdentifier data type.

The problem that we are running into, is that different applications interpret the UniqueIdentifier data type differently - ADO 2.x interprets it with the opening and closing { } braces. ADO.NET does not include the { and } in the GUID structure, SQL Server Query Analyzer does not include the { } braces, and SQL Server Enterprise Manage does include the { }

so we end up with results like this:

ADO 2.x: {012345678-abcd-ef01-2345-6789abcd}
ADO.NET: 012345678-abcd-ef01-2345-6789abcd
Query Analyzer: 012345678-abcd-ef01-2345-6789abcd
Enterprise Manager: {012345678-abcd-ef01-2345-6789abcd}

The problem with this is in two parts:
1) VB6 does not have a GUID structure or data type, so we are treating GUIDs as strings... but VB6 doesn't recognize "{012345678-abcd-ef01-2345-6789abcd}" and "012345678-abcd-ef01-2345-6789abcd" as the same string
2) when sending a URL link through an email, the { } braces break the link - in all email applications that we have tested. This include Groupwise 6, Groupwise 6.5, IPSwitch Web IMail, Outlook 2000, and Outlook Express (IE 6).

We're becoming very frustrated with the problems at hand, and need to know how others have worked around these problems. Please respond with any kind of advice or any real life situations where you have encountered this and found a solution, etc.

Thanks.The workaround is simple: program around it.

Standardize on the "new form" without curly braces and never show the m in any other way.

In "old vb" this means writing two little transform methods. Hardly a hugh task.|||so you're basically saying that every application we ever write with GUIDs has to have special processing code inside of it to handle guids?

that doesn't seem to be very smart, if you ask me.|||Well every application you ever write will not connect to several different types of data sources, will it?

And the 2 odd lines of code in every application... is too much? Really? Then I question the need for GUIDs in your application. Why did you feel the need to use them?

Posted by justifylzxw at 1:55 AM 0 comments
Labels: , , , , , , , , , , , , , ,

Wednesday, March 21, 2012

Front end display of two fields

I am doing a report which needs to display a field like

totals/percentages...

the actual data values is something like this

=(Sum(Fields!B.Value))& " / " &

(Sum(Fields!B.Value))/(sum(Fields!A.Value) + sum(Fields!B.Value)+sum(Fields!C.Value)+sum(Fields!D.Value)+sum(Fields!E.Value)+sum(Fields!F.Value))

*100

the problem is sum (fields!B.value) is an integer (sum of students) like 32

and the aggregate value in the denominator is a percentage value (0.014343443%)

I need to approximate the value to 0.014

something like this

totals/percentages... .......32/0,014 instead of 32 / 0.014343443 in the front end of the report properties

Please help

Thanks

Wouldn't math.Round() do the trick? You just want to round the decimal to three decimal places....Right?|||

="32" & " / " & round(0.014343443, 3)

Posted by justifylzxw at 2:58 AM 0 comments
Labels: , , , , , , , , , , , , ,

Wednesday, March 7, 2012

FREETEXTTABLE on more than one indexed fields

Is it possible to do a FT search on MULTIPLE fields in an
Index? I know that the FREETEXTTABLE can only do it on either one or all
indexed columns. But what if i want to do it for more columns.
Or is it possible to create a second FT-INDEX for the same table? I want to
index different fields in each one to use it in different areas.
Note: Full-Text seach MUST be used. I know I could write an OR statement,
but it must be done with a FT search. Any ideas?Denis,
See, my reply in the fulltext newsgroup, but yes, this can be done, see "SQL
Server FTS across multiple tables or columns" at:
http://spaces.msn.com/members/jtkane/Blog/cns!1pWDBCiDX1uvH5ATJmNCVLPQ!316.e
ntry
and substitute freetexttable for containstable in the examples.
Thanks,
John
--
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Denis" <denis@.pharmiweb.com> wrote in message
news:uzC6lOJKFHA.1340@.TK2MSFTNGP10.phx.gbl...
> Is it possible to do a FT search on MULTIPLE fields in an
> Index? I know that the FREETEXTTABLE can only do it on either one or all
> indexed columns. But what if i want to do it for more columns.
> Or is it possible to create a second FT-INDEX for the same table? I want
to
> index different fields in each one to use it in different areas.
>
> Note: Full-Text seach MUST be used. I know I could write an OR
statement,
> but it must be done with a FT search. Any ideas?
>|||I looked at what you said but I am not sure how that would work. Let me
give you an idea of the type of how i am trying to do the search.
-- Old Search
--SET @.dynQuery = @.dynQuery + ' INNER JOIN
FREETEXTTABLE(tblJobsDataWareHouse, *, ''' + @.Keywords + ''') as KW ON
FT_TBL.uID = KW.[KEY]'
--SET @.RankField = 'KW.RANK'
-- NEW SEARCH
SET @.dynQuery = @.dynQuery + ' INNER JOIN
FREETEXTTABLE(tblJobsDataWareHouse, fldRequirementshtm, ''' + @.Keywords +
''') as KW ON FT_TBL.uID = KW.[KEY] '
SET @.RankField = 'KW.RANK'
SET @.dynQuery = @.dynQuery + ' FULL OUTER JOIN
FREETEXTTABLE(tblJobsDataWareHouse, fldJobTitle, ''' + @.Keywords + ''') as
KWw ON FT_TBL.uID = KWw.[KEY]'
SET @.RankField = 'KWw.RANK'
SET @.dynQuery = @.dynQuery + ' FULL OUTER JOIN
FREETEXTTABLE(tblJobsDataWareHouse, fldCompanyName, ''' + @.Keywords + ''')
as KWww ON FT_TBL.uID = KWww.[KEY]'
SET @.RankField = 'KWww.RANK'
I want my new search to look at Title, Description and Company name only,
rather than every field that was included.
Posted by justifylzxw at 6:02 AM 0 comments
Labels: , , , , , , , , , , , , ,

freetextable and multiple tables

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
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.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"shank" <shank@.tampabay.rr.com> wrote in message
news:u9IDOK6ZEHA.2516@.TK2MSFTNGP10.phx.gbl...
> 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
>
|||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
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:uzlWtk6ZEHA.3476@.tk2msftngp13.phx.gbl...
> 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[vbcol=seagreen]
> by MSSearch will be handled by the calling application and in some cases
> will not be trappable with the @.@.error system variable.
>
>
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "shank" <shank@.tampabay.rr.com> wrote in message
> news:u9IDOK6ZEHA.2516@.TK2MSFTNGP10.phx.gbl...
the[vbcol=seagreen]
> FT
> it,
> searching
@.SearchCriteria).
> At
> all
> ItemTitles.OrderNo
>
|||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
|||Shank,
I've been able to use the below table structures and inserted some sample
data and test your query along with the below modified queries. I've also
attached a .SQL script file - Containstable_multiple_tables.sql that
contains the below queries as well as sample data.
I believe that the issue here is two fold - 1) using a common variable
(@.SearchCriteria) where a single word must exist in all tables columns, and
2) your SQL FTS FREETEXTTABLE query only references one table (ItemStock),
while the below Modified Original query using a common word (row) return the
search word from all three tables and is more flexible as you can use both
an OR as well as an AND condition between the freetexttable or containstable
clauses. Please, review carefully the attached sql file, the data as well
as the queries and experiment with them on either Win2003 or WinXP machines
as you may get different results, due to the os-supplied wordbreaker
depending upon your actual data.
-- Original problem query, works with the above data, but only with a common
word "row" is queried from all three tables.
DECLARE @.SearchCriteria varchar(100)
SET @.SearchCriteria = ' "row*" ' -- primary search column is ItemStock.Label
(other columns, left NULL)
SELECT ItemStock.OrderNo,ItemStock.Label
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
ORDER BY FS_TABLE.Rank DESC
-- 1st test: 4 rows becasue only ItemStock is referenced in FREETEXTTABLE
clasue
-- Modifed Original query
DECLARE @.SearchCriteria varchar(100)
SET @.SearchCriteria = ' "row*" ' -- Test with both containstable & freetext
AND OR Betewen for expected results
SELECT distinct e.OrderNo, e.Label -- disctinct requried to get 5 rows,
still not exactly same as above...
from ItemStock AS e, ItemTitles t, ItemHardware h,
freetexttable(ItemStock, Label, @.SearchCriteria) as A,
freetexttable(ItemTitles, Title, @.SearchCriteria) as B,
freetexttable(ItemHardware, s_page, @.SearchCriteria) as C
where
A.[KEY] = e.OrderNo and
B.[KEY] = t.OrderNo and
C.[KEY] = h.OrderNo
-- 1st test: NOT the same as above as this query gets 5 rows, additional row
is "This is Label 0005 of row five Interface"
Regards,
John
"shank" <shank@.tampabay.rr.com> wrote in message
news:NKEIc.15669$KP6.716193@.twister.tampabay.rr.co m...
> 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
>
begin 666 Containstable_mutiple_tables.sql
M#0HM+0EF:6QE;F%M93H@.0V]N=&%I;G-T86)L95]M=71I<&QE7W1A8FQE<RYS
M<6P-"BTM"7!U<G!O<V4Z('1O(&1O8W5M96YT(&AO=R!T;R!U<V4@.8V ]N=&%I
M;G-T86)L92!A;F0@.9G)E971E>'1T86)L92!I;B!M=71I<&QE($944 R!Q=65R
M:65S#0HM+0EM;V1I9FEE9#H@.,3 Z,#4@.4$T@.-R\Q,B\R,# T#0H-"G5S92!S
M<6QF=',-"F=O#0IS96QE8W0@.0$!V97)S:6]N("TM($UI8W)O<V]F="!344P@.
M4V5R=F5R(" R,# P("T@.."XP,"XW-C @.;VX@.16YT97)P<FES92!%9&ET:6]N
M(&]N(%=I;F1O=W,@.3E0@.-2XR("A"=6EL9" S-SDP.B I#0HO*@.T*+2TM+2TM
M+2TM+2TM+2TM+2TM+2TM+2TM+2TM+2TM+2TM+2TM+2TM+2TM+ 2TM#0I-:6-R
M;W-O9G0@.4U%,(%-E<G9E<B @.,C P," M(#@.N,# N-S8P("A);G1E;"!8.#8I
M("T@.1&5V96QO<&5R($5D:71I;VX@.;VX@.5VEN9&]W<R!.5" U+C$@.*$)U:6QD
M(#(V,# Z(%-E<G9I8V4@.4&%C:R Q*0T*4$LO1DL@.<F5L871I;VYS:&EP(&)E
M='=E96X@.271E;5-T;V-K+D]R9&5R3F\@./2!)=&5M5&ET;&5S+D]R9&5R3F\@.
M86YD(&%L<V\@.271E;5-T;V-K+D]R9&5R3F\@./2!)=&5M2&%R9'=A<F4N3W)D
M97).;RX@.#0HJ+PT*#0HM+2!$969I;F4@.=&%B;&5S+BXN#0I#4 D5!5$4@.5$%"
M3$4@.6V1B;UTN6TET96U3=&]C:UT@.* T*(%M/<F1E<DYO72!;=F%R8VAA<ET@.
M*#4P*2!#3TQ,051%(%-13%],871I;C%?1V5N97)A;%]#4#%?0TE?05,@.3D]4
M($Y53$P@.+" M+2!02R!)=&5M4W1O8VL@.+2!U;FEQ=64@./PT*(%M,86)E;%T@.
M6W9A<F-H87)=("@.U,"D@.0T],3$%412!344Q?3&%T:6XQ7T=E;F5R86Q?0U Q
M7T-)7T%3($Y/5"!.54Q,("P-"B!;4V]F=$AA<F1=(%MV87)C:&%R72 H-3 I
M($-/3$Q!5$4@.4U%,7TQA=&EN,5]'96YE<F%L7T-0,5]#25]!4R!.54Q,("P-
M"B!;36%N0V]D95T@.6W9A<F-H87)=("@.U,"D@.0T],3$%412!344Q?3&%T:6XQ
M7T=E;F5R86Q?0U Q7T-)7T%3($Y53$P@.+ T*(%M$97-C<FEP=&EO;ET@.6W9A
M<F-H87)=("@.U,"D@.0T],3$%412!344Q?3&%T:6XQ7T=E;F5R86Q?0U Q7T-)
M7T%3($Y53$P@.+ T*(%MD7U5P9&%T95T@.6V1A=&5T:6UE72!.54Q,("P-"B!;
M17AC;'5S:79E72!;=F%R8VAA<ET@.*#4P*2!#3TQ,051%(%-13%],871I;C%?
M1V5N97)A;%]#4#%?0TE?05,@.3E5,3" L#0H@.6T%F9FEL:6%T95T@.6W9A<F-H
M87)=("@.U,"D@.0T],3$%412!344Q?3&%T:6XQ7T=E;F5R86Q?0U Q7T-)7T%3
M($Y53$P@.+ T*(%M.97=296QE87-E72!;=F%R8VAA<ET@.*#4P*2!#3TQ,051%
M(%-13%],871I;C%?1V5N97)A;%]#4#%?0TE?05,@.3E5,3" L#0H@.6U-T871U
M<UT@.6W9A<F-H87)=("@.U,"D@.0T],3$%412!344Q?3&%T:6XQ7T=E;F5R86Q?
M0U Q7T-)7T%3($Y53$P@.+ T*(%M);G9=(%MD96-I;6%L72@.U+" P*2!.54Q,
M("P-"B!;9%]$871E72!;9&%T971I;65=($Y53$P@.+ T*(%M#871E9V]R>5T@.
M6W9A<F-H87)=("@.U,"D@.0T],3$%412!344Q?3&%T:6XQ7T=E;F5R86Q?0U Q
M7T-)7T%3($Y53$P@.+ T*(%M3:6YG;&5!<G1I<W1=(%MV87)C:&%R72 H,RD@.
M0T],3$%412!344Q?3&%T:6XQ7T=E;F5R86Q?0U Q7T-)7T%3($Y53$P@.+ T*
M(%MS7U1Y<&5=(%MV87)C:&%R72 H-3 I($-/3$Q!5$4@.4U%,7TQA=&EN,5]'
M96YE<F%L7T-0,5]#25]!4R!.54Q,("P-"B!;5'EP941E<V-R:7!=(%MV87)C
M:&%R72 H,3 P*2!#3TQ,051%(%-13%],871I;C%?1V5N97)A;%]#4#%?0TE?
M05,@.3E5,3" L#0H@.6T-A<V5D72!;=F%R8VAA<ET@.*#4P*2!#3TQ,051%(%-1
M3%],871I;C%?1V5N97)A;%]#4#%?0TE?05,@.3E5,3" L#0H@.6U!A8VME9%T@.
M6W9A<F-H87)=("@.U,"D@.0T],3$%412!344Q?3&%T:6XQ7T=E;F5R86Q?0U Q
M7T-)7T%3($Y53$P@.+ T*(%M-86YU9ET@.6W9A<F-H87)=("@.U,"D@.0T],3$%4
M12!344Q?3&%T:6XQ7T=E;F5R86Q?0U Q7T-)7T%3($Y53$P@.+ T*(%M-86Y3
M;W)T72!;:6YT72!.54Q,("P-"B!;5V5I9VAT72!;9&5C:6UA;%TH-2P@.,BD@.
M3E5,3" L#0H@.6TEC;VYS-S5=(%MV87)C:&%R72 H,3 P*2!#3TQ,051%(%-1
M3%],871I;C%?1V5N97)A;%]#4#%?0TE?05,@.3E5,3" L#0H@.6TEC;VYS,3 P
M72!;=F%R8VAA<ET@.*#$P,"D@.0T],3$%412!344Q?3&%T:6XQ7T=E;F5R86Q?
M0U Q7T-)7T%3($Y53$P@.+ T*(%M)8V]N<S(P,%T@.6W9A<F-H87)=("@.Q,# I
M($-/3$Q!5$4@.4U%,7TQA=&EN,5]'96YE<F%L7T-0,5]#25]!4R!.54Q,("P-
M"B!;26-O;G,S,#!=(%MV87)C:&%R72 H,3 P*2!#3TQ,051%(%-13%],871I
M;C%?1V5N97)A;%]#4#%?0TE?05,@.3E5,3" L#0H@.6TE$72!;;G5M97)I8UTH
M,3 L(# I($E$14Y42519("@.Q+" Q*2!.3U0@.3E5,3" M+2TM+2TM+2TM+2TM
M+2TM+2TM+2TM+2TM+2TM/B!O<B!02R _(&IU<W0@.9F]R($94($EN9&5X(#\-
M"BD@.3TX@.6U!224U!4EE=#0I'3PT*04Q415(@.5$%"3$4@.271E; 5-T;V-K(%=)
M5$@.@.3D]#2$5#2R!!1$0@.4%))34%262!+15D@.0TQ54U1%4D5$("A/<F1E<DYO
M*0T*9V\-"BTM($EN<V5R="!T97-T('-A;7!L92!D871A#0II;G-E<G0@.:6YT
M;R!)=&5M4W1O8VL@.=F%L=65S("@.G,# P,2<L("=4:&ES(&ES($QA8F5L(# P
M,#$@.;V8@.<F]W(&]N92!":6QL>2!*;V5L)RQ.54Q,+$Y53$PL3E5,3"Q.54Q,
M+$Y53$PL3E5,3"Q.54Q,+$Y53$PL3E5,3"Q.54Q,+$Y53$PL3 E5,3"Q.54Q,
M+$Y53$PL3E5,3"Q.54Q,+$Y53$PL3E5,3"Q.54Q,+$Y53$PL3 E5,3"Q.54Q,
M+$Y53$PI#0II;G-E<G0@.:6YT;R!)=&5M4W1O8VL@.=F%L=65S("@.G,# P,B<L
M("=4:&ES(&ES($QA8F5L(# P,#(@.;V8@.<F]W('1W;R!3=&EN9R<L3E5,3"Q.
M54Q,+$Y53$PL3E5,3"Q.54Q,+$Y53$PL3E5,3"Q.54Q,+$Y53 $PL3E5,3"Q.
M54Q,+$Y53$PL3E5,3"Q.54Q,+$Y53$PL3E5,3"Q.54Q,+$Y53 $PL3E5,3"Q.
M54Q,+$Y53$PL3E5,3"Q.54Q,*0T*:6YS97)T(&EN=&\@.271E; 5-T;V-K('9A
M;'5E<R H)S P,#,G+" G5&AI<R!I<R!,86)E;" P,# S(&]F(')O=R!T:')E
M92!344P@.4V5R=F5R)RQ.54Q,+$Y53$PL3E5,3"Q.54Q,+$Y53 $PL3E5,3"Q.
M54Q,+$Y53$PL3E5,3"Q.54Q,+$Y53$PL3E5,3"Q.54Q,+$Y53 $PL3E5,3"Q.
M54Q,+$Y53$PL3E5,3"Q.54Q,+$Y53$PL3E5,3"Q.54Q,+$Y53 $PI#0II;G-E
M<G0@.:6YT;R!)=&5M4W1O8VL@.=F%L=65S("@.G,# P-"<L("=4:&ES(&ES($QA
M8F5L(# P,#0@.;V8@.<F]W(&9O=7(@.3U)!0TQ%)RQ.54Q,+$Y53$PL3E5,3"Q.
M54Q,+$Y53$PL3E5,3"Q.54Q,+$Y53$PL3E5,3"Q.54Q,+$Y53 $PL3E5,3"Q.
M54Q,+$Y53$PL3E5,3"Q.54Q,+$Y53$PL3E5,3"Q.54Q,+$Y53 $PL3E5,3"Q.
M54Q,+$Y53$PI#0HM+2!R;W<@.3D]4(&EN($ET96U4:71L97,L(&)U="!I<R!I
M;B!)=&5M2&%R9'=A<F4-"FEN<V5R="!I;G1O($ET96U3=&]C:R!V86QU97,@.
M*"<P,# U)RP@.)U1H:7,@.:7,@.3&%B96P@.,# P-2!O9B!R;W<@.9FEV92!);G1E
M<F9A8V4G+$Y53$PL3E5,3"Q.54Q,+$Y53$PL3E5,3"Q.54Q,+ $Y53$PL3E5,
M3"Q.54Q,+$Y53$PL3E5,3"Q.54Q,+$Y53$PL3E5,3"Q.54Q,+ $Y53$PL3E5,
M3"Q.54Q,+$Y53$PL3E5,3"Q.54Q,+$Y53$PL3E5,3"D-"F=O#0H-"D-214%4
M12!404),12!;9&)O72Y;271E;51I=&QE<UT@.* T*(%M/<F1E<DYO72!;=F%R
M8VAA<ET@.*#4P*2!#3TQ,051%(%-13%],871I;C%?1V5N97)A;%]#4#%?0TE?
M05,@.3D]4($Y53$P@.+ T*(%M4:71L95T@.6W9A<F-H87)=("@.W,"D@.0T],3$%4
M12!344Q?3&%T:6XQ7T=E;F5R86Q?0U Q7T-)7T%3($Y/5"!.54Q,("P-"B!;
M07)T:7-T72!;=F%R8VAA<ET@.*#8P*2!#3TQ,051%(%-13%],871I;C%?1V5N
M97)A;%]#4#%?0TE?05,@.3D]4($Y53$P@.+ T*(%M,;V-A=&EO;ET@.6W9A<F-H
M87)=("@.U,"D@.0T],3$%412!344Q?3&%T:6XQ7T=E;F5R86Q?0U Q7T-)7T%3
M($Y/5"!.54Q,("P-"B!;4V]R=$ME>5T@.6V1E8VEM86Q=*#4L(# I($Y/5"!.
M54Q,("P-"B!;1&ES8U])1%T@.6W9A<F-H87)=("@.U,"D@.0T],3$%412!344Q?
M3&%T:6XQ7T=E;F5R86Q?0U Q7T-)7T%3($Y53$P@.+ T*(%M-4#-&:6QE<UT@.
M6W9A<F-H87)=("@.Q,# I($-/3$Q!5$4@.4U%,7TQA=&EN,5]'96YE<F%L7T-0
M,5]#25]!4R!.54Q,("P-"B!;241=(%MN=6UE<FEC72@.Q,"P@.,"D@.241%3E1)
M5%D@.*#$L(#$I($Y/5"!.54Q,#0HI($].(%M04DE-05)970T*1T\-"D%,5$52
M(%1!0DQ%($ET96U4:71L97,@.5TE42"!.3T-(14-+($%$1"!04DE-05)9($M%
M62!#3%535$52140@.*$]R9&5R3F\I#0IG;PT*+2T@.26YS97)T('1E<W0@.<V%M
M<&QE(&1A=&$-"FEN<V5R="!I;G1O($ET96U4:71L97,@.=F%L=65S("@.G,# P
M,2<L)U1H:7,@.:7,@.5&ET;&4@.,# P,2!O9B!R;W<@.;VYE(%-T<F%N9V5R)RPG
M0FEL;'D@.2F]E;"<L)R!3;VUE<&QA8V4@.:&5R92<L,2Q.54Q,+$Y53$PI#0I I
M;G-E<G0@.:6YT;R!)=&5M5&ET;&5S('9A;'5E<R H)S P,#(G+"=4:&ES(&ES
M(%1I=&QE(# P,#(@.;V8@.<F]W('1W;R!3=&EN9R<L)U-T:6YG)RPG(%-O;65P
M;&%C92!T:&5R92<L.2Q.54Q,+$Y53$PI#0II;G-E<G0@.:6YT;R!)=&5M5&ET
M;&5S('9A;'5E<R H)S P,#,G+"=4:&ES(&ES(%1I=&QE(# P,#,@.;V8@.<F]W
M('1H<F5E(%-13"!397)V97(G+"=-:6-R;W-O9G0G+"<@.4V]M97!L86-E(&5L
M<V4G+#@.L3E5,3"Q.54Q,*0T*:6YS97)T(&EN=&\@.271E;51I= &QE<R!V86QU
M97,@.*"<P,# T)RPG5&AI<R!I<R!4:71L92 P,# T(&]F(')O=R!F;W5R($]2
M04-,12<L)T]R86-L92<L)R!3;VUE<&QA8V4@.96QS92<L-"Q.54Q,+$Y53$PI
M#0IG;PT*#0HM+2!)=&5M4W1O8VLN3W)D97).;R ]($ET96U(87)D=V%R92Y/
M<F1E<DYO+B -"D-214%412!404),12!;9&)O72Y;271E;4AA<F1W87)E72 H
M#0H@.6T]R9&5R3F]=(%MV87)C:&%R72 H-3 I($-/3$Q!5$4@.4U%,7TQA=&EN
M,5]'96YE<F%L7T-0,5]#25]!4R!.3U0@.3E5,3" L#0H@.6W-?<&%G95T@.6W9A
M<F-H87)=("@.V,"D@.0T],3$%412!344Q?3&%T:6XQ7T=E;F5R86Q?0U Q7T-)
M7T%3($Y53$P@.+ T*(%MS7U1H=6UB72!;=F%R8VAA<ET@.*#$U,"D@.0T],3$%4
M12!344Q?3&%T:6XQ7T=E;F5R86Q?0U Q7T-)7T%3($Y53$P@.+ T*(%MS7W!I
M8UT@.6W9A<F-H87)=("@.Q-3 I($-/3$Q!5$4@.4U%,7TQA=&EN,5]'96YE<F%L
M7T-0,5]#25]!4R!.54Q,("P-"B!;4$1&4V-A;&5=(%MV87)C:&%R72 H-3 I
M($-/3$Q!5$4@.4U%,7TQA=&EN,5]'96YE<F%L7T-0,5]#25]!4R!.54Q,("P-
M"B!;;5]3<&5C<UT@.6W9A<F-H87)=("@.X,# P*2!#3TQ,051%(%-13%],871I
M;C%?1V5N97)A;%]#4#%?0TE?05,@.3E5,3" L#0H@.6U!$1E-P96-S72!;=F%R
M8VAA<ET@.*#@.P,# I($-/3$Q!5$4@.4U%,7TQA=&EN,5]'96YE<F%L7T-0,5]#
M25]!4R!.54Q,("P-"B!;3F]T97-=(%MV87)C:&%R72 H.# P,"D@.0T],3$%4
M12!344Q?3&%T:6XQ7T=E;F5R86Q?0U Q7T-)7T%3($Y53$P@.+ T*(%M-961I
M85T@.6W9A<F-H87)=("@.S*2!#3TQ,051%(%-13%],871I;C%?1V5N97)A;%]#
M4#%?0TE?05,@.3E5,3" L#0H@.6T-A<W-=(%MV87)C:&%R72 H,RD@.0T],3$%4
M12!344Q?3&%T:6XQ7T=E;F5R86Q?0U Q7T-)7T%3($Y53$P@.+ T*(%M#1$==
M(%MV87)C:&%R72 H,RD@.0T],3$%412!344Q?3&%T:6XQ7T=E;F5R86Q?0U Q
M7T-)7T%3($Y53$P@.+ T*(%M60T1=(%MV87)C:&%R72 H,RD@.0T],3$%412!3
M44Q?3&%T:6XQ7T=E;F5R86Q?0U Q7T-)7T%3($Y53$P@.+ T*(%M$5D1=(%MV
M87)C:&%R72 H,RD@.0T],3$%412!344Q?3&%T:6XQ7T=E;F5R86Q?0U Q7T-)
M7T%3($Y53$P@.+ T*(%M)1%T@.6VYU;65R:6-=*#$P+" P*2!)1$5.5$E462 H
M,2P@.,2D@.3D]4($Y53$P-"BD@.3TX@.6U!224U!4EE=#0I'3PT*04Q415(@.5$%"
M3$4@.271E;4AA<F1W87)E(%=)5$@.@.3D]#2$5#2R!!1$0@.4%))34%262!+15D@.
M0TQ54U1%4D5$("A/<F1E<DYO*0T*9V\-"B\J("TM($=O="!T:&4@.9F]L;&]W
M:6YG('=A<FYI;F<@.=VAE;B!C<F5A=&EN9R!T86)L93H@.271E; 4AA<F1W87)E
M#0I787)N:6YG.B!4:&4@.=&%B;&4@.)TET96U(87)D=V%R92<@.: &%S(&)E96X@.
M8W)E871E9"!B=70@.:71S(&UA>&EM=6T@.<F]W('-I>F4@.*#(T-3,R*2!E>&-E
M961S('1H92!M87AI;75M(&YU;6)E<B!O9B!B>71E<R!P97(@.< F]W("@.X,#8P
M*2X@.#0I)3E-%4E0@.;W(@.55!$051%(&]F(&$@.<F]W(&EN('1H:7,@.=&%B;&4@.
M=VEL;"!F86EL(&EF('1H92!R97-U;'1I;F<@.<F]W(&QE;F=T:"!E>&-E961S
M(#@.P-C @.8GET97,N#0HJ+PT*+2T@.26YS97)T(%-A;7!L92!D871A#0II;G-E
M<G0@.:6YT;R!)=&5M2&%R9'=A<F4@.=F%L=65S("@.G,# P,2<L("=4:&ES(&ES
M($QA8F5L(# P,#$@.;V8@.<F]W(&]N92!3=')A;F=E<B<L3E5,3"Q.54Q,+$Y5
M3$PL3E5,3"Q.54Q,+$Y53$PL3E5,3"Q.54Q,+$Y53$PL3E5,3 "Q.54Q,*0T*
M:6YS97)T(&EN=&\@.271E;4AA<F1W87)E('9A;'5E<R H)S P,#(G+" G5&AI
M<R!I<R!,86)E;" P,# R(&]F(')O=R!T=V\@.4W1I;F<G+$Y53$PL3E5,3"Q.
M54Q,+$Y53$PL3E5,3"Q.54Q,+$Y53$PL3E5,3"Q.54Q,+$Y53 $PL3E5,3"D-
M"FEN<V5R="!I;G1O($ET96U(87)D=V%R92!V86QU97,@.*"<P, # S)RP@.)U1H
M:7,@.:7,@.3&%B96P@.,# P,R!O9B!R;W<@.=&AR964@.4U%,(%-E<G9E<B<L3E5,
M3"Q.54Q,+$Y53$PL3E5,3"Q.54Q,+$Y53$PL3E5,3"Q.54Q,+ $Y53$PL3E5,
M3"Q.54Q,*0T*:6YS97)T(&EN=&\@.271E;4AA<F1W87)E('9A; '5E<R H)S P
M,#0G+" G5&AI<R!I<R!,86)E;" P,# T(&]F(')O=R!F;W5R($]204-,12<L
M3E5,3"Q.54Q,+$Y53$PL3E5,3"Q.54Q,+$Y53$PL3E5,3"Q.5 4Q,+$Y53$PL
M3E5,3"Q.54Q,*0T*+2T@.<F]W($Y/5"!I;B!)=&5M5&ET;&5S+"!B=70@.:7,@.
M:6X@.271E;5-T;V-K#0II;G-E<G0@.:6YT;R!)=&5M2&%R9'=A<F4@.=F%L=65S
M("@.G,# P-2<L("=4:&ES(&ES($QA8F5L(# P,#4@.;V8@.<F]W(&9I=F4@.26YT
M97)F86-E)RQ.54Q,+$Y53$PL3E5,3"Q.54Q,+$Y53$PL3E5,3"Q.54Q,+ $Y5
M3$PL3E5,3"Q.54Q,+$Y53$PI#0IG;PT*#0HM+2!C;VYF:7)M( '-A;7!L92!D
M871A(&%N9"!&5"UE;F%B;&4@.=&%B;&5S('9I82!%;G1E<G!R: 7-E($UA;F%G
M97(N+BX-"G-E;&5C=" J(&9R;VT@.271E;5-T;V-K#0HM+2!T<G5N8V%T92!T
M86)L92!)=&5M4W1O8VL-"G-E;&5C=" J(&9R;VT@.271E;51I=&QE<PT*+2T@.
M=')U;F-A=&4@.=&%B;&4@.271E;51I=&QE<PT*<V5L96-T("H@.9G)O;2!)=&5M
M2&%R9'=A<F4-"BTM('1R=6YC871E('1A8FQE($ET96U(87)D=V%R90T*9V \-
M"@.T*+2T@.#0HM+2!/<FEG:6YA;"!P<F]B;&5M('%U97)Y+"!W;W)K<R!W:71H
M('1H92!A8F]V92!D871A+"!B=70@.;VYL>2!W:71H(&$@.8V]M;6]N('=O<F0@.
M(G)O=R(@.:7,@.<75E<FEE9"!F<F]M(&%L;"!T:')E92!T86)L97,N#0I$14-,
M05)%($!396%R8VA#<FET97)I82!V87)C:&%R*#$P,"D-"E-%5"! 4V5A<F-H
M0W)I=&5R:6$@./2 G(")R;W<J(B G("TM('!R:6UA<GD@.<V5A<F-H(&-O;'5M
M;B!I<R!)=&5M4W1O8VLN3&%B96P@.*&]T:&5R(&-O;'5M;G,L(&QE9G0@.3E5,
M3"D-"E-%3$5#5"!)=&5M4W1O8VLN3W)D97).;RQ)=&5M4W1O8VLN3&%B9 6P@.
M#0I&4D]-($ET96U3=&]C:PT*("!)3DY%4B!*3TE.($ET96U4:71L97,@.("!/
M3B!)=&5M4W1O8VLN3W)D97).;R ]($ET96U4:71L97,N3W)D97).;PT*("!)
M3DY%4B!*3TE.($ET96U(87)D=V%R92!/3B!)=&5M4W1O8VLN3W)D97).;R ]
M($ET96U(87)D=V%R92Y/<F1E<DYO#0H@.($E.3D52($I/24X@.1E)%151%6%14
M04),12A)=&5M4W1O8VLL("HL($!396%R8VA#<FET97)I82D@.0 5,@.1E-?5$%"
M3$4@.3TX@.1E-?5$%"3$4N6TM%65T@./2!)=&5M4W1O8VLN3W)D97).;PT*(" @.
M("!/4D1%4B!"62!&4U]404),12Y286YK($1%4T,-"BTM(#%S="!T97-T.B T
M(')O=W,@.8F5C87-U92!O;FQY($ET96U3=&]C:R!I<R!R969E<F5N8V5D(&EN
M($92145415A45$%"3$4@.8VQA<W5E#0H-"@.T*+2T@.36]D:69E9"!/<FEG:6YA
M;"!Q=65R>0T*1$5#3$%212! 4V5A<F-H0W)I=&5R:6$@.=F%R8VAA<B@.Q,# I
M#0I3150@.0%-E87)C:$-R:71E<FEA(#T@.)R B<F]W*B(@.)R @.+2T@.5&5S="!W
M:71H(&)O=&@.@.8V]N=&%I;G-T86)L92 F(&9R965T97AT($%.1"!/4B!"971E
M=V5N(&9O<B!E>'!E8W1E9"!R97-U;'1S#0I314Q%0U0@.9&ES=&EN8W0@.92Y/
M<F1E<DYO+"!E+DQA8F5L("TM(&1I<V-T:6YC="!R97%U<FEE9"!T;R!G970@.
M-2!R;W=S+"!S=&EL;"!N;W0@.97AA8W1L>2!S86UE(&%S(&%B;W9 E+BXN#0IF
M<F]M($ET96U3=&]C:R!!4R!E+"!)=&5M5&ET;&5S('0L($ET96U(87)D=V%R
M92!H+ T*(" @.("!F<F5E=&5X='1A8FQE*$ET96U3=&]C:RP@.3&%B96PL($!3
M96%R8VA#<FET97)I82D@.87,@.02P-"B @.(" @.9G)E971E>'1T86)L92A)=&5M
M5&ET;&5S+"!4:71L92P@.0%-E87)C:$-R:71E<FEA*2!A<R!"+ T*(" @.("!F
M<F5E=&5X='1A8FQE*$ET96U(87)D=V%R92P@.<U]P86=E+"! 4V5A<F-H0W)I
M=&5R:6$I(&%S($,-"B @.(" @.("!W:&5R90T*(" @.(" @.(" @.02Y;2T5972 ]
M(&4N3W)D97).;R!A;F0-"B @.(" @.(" @.($(N6TM%65T@./2!T+D]R9&5R3F\@.
M86YD#0H@.(" @.(" @.("!#+EM+15E=(#T@.:"Y/<F1E<DYO#0HM+2 Q<W0@.=&5S
M=#H@.3D]4('1H92!S86UE(&%S(&%B;W9E(&%S('1H:7,@.<75E<GD@.9V5T< R U
M(')O=W,L(&%D9&ET:6]N86P@.<F]W(&ES(")4:&ES(&ES($QA8F5L(# P,#4@.
M;V8@.<F]W(&9I=F4@.26YT97)F86-E(@.T*#0H-"BTM(%1E<W0@.=VET:"!B;W1H
M(&-O;G1A:6YS=&%B;&4@.)B!F<F5E=&5X="!!3D0@.3U(@.0F5T97=E; B!E86-H
M(0T*4T5,14-4(&1I<W1I;F-T(&4N3W)D97).;RP@.92Y,86)E; T*9G)O;2!)
M=&5M4W1O8VL@.05,@.92P@.271E;51I=&QE<R!T+ T*(" @.("!C;VYT86EN<W1A
M8FQE*$ET96U3=&]C:RP@.3&%B96PL("=":6QL>2<I(&%S($$L#0H@.(" @.(&-O
M;G1A:6YS=&%B;&4H271E;51I=&QE<RP@.5&ET;&4L("=3=')A; F=E<B<I(&%S
M($(@.+2T@.;F]W(&=E='1I;F<@.<F5S=6QT<R A($UU<W0@.:&%V92!C;VQU;6XM
M<W!E8VEF:6,@.<V5A<F-H('=O<F0A#0H@.(" @.(" @.=VAE<F4-"B @.(" @.(" @.
M($$N6TM%65T@./2!E+D]R9&5R3F\@.;W(@.+2T@.3U(@.+2T@.9V5N97)A=&5S(&QA
M<F=E<B!R97-U;'0L('-O('5S92!D:7-T:6YC="!E+D]R9&5R3F\L(')E='5R
M;G,@.-2!R;W=S('=I=&@.@.9&ES=&EN8W0-"B @.(" @.(" @.($(N6TM%65T@./2!T
M+D]R9&5R3F\-"@.T*<V5L96-T("H@.9G)O;2!)=&5M4W1O8VL@.=VAE<F4@.8V]N
M=&%I;G,H*BPG0FEL;'DG*2 M+2!R971U<FYS(#$@.<F]W+@.T*#0H-"BTM($UO
M9&EF:65D($]R:6=I;F%L('%U97)Y+"!R96UO=F5D('1H92! 4V5A<F-H0W)I
M=&5R:6$@.=F%R:6%B;&4@.=&\@.=&5S="!W:71H('1A8FQE(&%N9 "!C;VQU;6XM
M<W!E8VEF:6,@.<V5A<F-H('=O<F1S#0I314Q%0U0@.9&ES=&EN8W0@.92Y/<F1E
M<DYO+"!E+DQA8F5L#0IF<F]M($ET96U3=&]C:R!!4R!E+"!)=&5M5&ET;&5S
M('0L($ET96U(87)D=V%R92!H+ T*(" @.("!C;VYT86EN<W1A8FQE*$ET96U3
M=&]C:RP@.3&%B96PL("=":6QL>2<I(&%S($$L#0H@.(" @.(&-O;G1A:6YS=&%B
M;&4H271E;51I=&QE<RP@.5&ET;&4L("=3=')A;F=E<B<I(&%S( $(L#0H@.(" @.
M(&-O;G1A:6YS=&%B;&4H271E;4AA<F1W87)E+"!S7W!A9V4L("=R; W<G*2!A
M<R!##0H@.(" @.(" @.=VAE<F4-"B @.(" @.(" @.($$N6TM%65T@./2!E+D]R9&5R
M3F\@.86YD("TM($]2(#T@.9V5N97)A=&5S(&UU=&EP;&4@.<F]W<RP@.86YD('1H
M97)E9F]R92!N965D<R @.9&ES=&EN8W0@.92Y/<F1E<DYO+@.T*(" @.(" @.(" @.
M0BY;2T5972 ]('0N3W)D97).;R!A;F0-"B @.(" @.(" @.($,N6TM%65T@./2!H
M+D]R9&5R3F\-"BTM(#%S="!T97-T.B P(')O=W,L('-A;64@.87,@.86)O=F4-
9"@.T*+2T@./&5O9CX-"@.T*#0H-"@.T*#0H-"@.``
`
end
Posted by justifylzxw at 6:01 AM 0 comments
Labels: , , , , , , , , , , , , , , ,

Free-Text Search "AND NOT" Wrong results - HELP, please!

Hi,
I have a Photo Database, with a Photo table. Each photo has several
Varchar fields for storing caption, description, keywords, etc. I have
a Full-Text index/catalog for these fields.
This is the problem:
I want to find all photos that are related to "Simon" and "Jane", but
no photos taken when they entered or exited the "clinic".
So, my Query looks like:
SELECT *
FROM Fotos LEFT JOIN CONTAINSTABLE(Fotos,*, 'ISABOUT("Simon") AND
ISABOUT("Jane") AND NOT ISABOUT("clinic")') AS KEY_TBL ON Fotos.Cod =
KEY_TBL.[KEY]
WHERE KEY_TBL.Rank>0
ORDER BY ISNULL(KEY_TBL.Rank,0) DESC, ISNULL(Fotos.FotoDate,
Fotos.CreatedDate) DESC
The problem is that it returns photos with descriptions that include
the word "Clinic" (in this particular query, the first row in the
resultset has the word clinic in it!).
I noticed "Simon", "Jane" and "clinic" could reside on different
columns, so I rewrote the query:
SELECT *
FROM Fotos LEFT JOIN CONTAINSTABLE(Fotos,*, 'Simon AND NOT clinic') AS
KEY_TBL0 ON Fotos.Cod = KEY_TBL0.[KEY]
LEFT JOIN CONTAINSTABLE(Fotos,*, Jane AND NOT clinic') AS KEY_TBL1 ON
Fotos.Cod = KEY_TBL1.[KEY]
WHERE KEY_TBL0.Rank+KEY_TBL1.Rank>0
ORDER BY KEY_TBL0.Rank+KEY_TBL1.Rank DESC, ISNULL(Fotos.FotoDate,
Fotos.CreatedDate) DESC
The same problem occurred.
What Am I doing wrong?
Thanks for your help.
JOEL
Thanks for your answer Dan.
I modified the query as follows:
SELECT *
FROM Fotos INNER JOIN CONTAINSTABLE(Fotos,*, 'Simon AND NOT clinic')
AS KEY_TBL0 ON Fotos.Cod = KEY_TBL0.[KEY]
INNER JOIN CONTAINSTABLE(Fotos,*, 'Jane AND NOT clinic') AS
KEY_TBL1 ON Fotos.Cod = KEY_TBL1.[KEY]
WHERE KEY_TBL0.Rank>0 AND KEY_TBL1.Rank>0
ORDER BY KEY_TBL0.Rank+KEY_TBL1.Rank DESC, ISNULL(Fotos.FotoDate,
Fotos.CreatedDate) DESC
I changed it to use Inner Join, but also ensured that any
Containstable returns significant photos, by having separate
Containstabel.Rank>0 in the WHERE clause. But it returns a row where a
field has something like "Simon and Jane entering the magnetic
resonance clinic...". I don't see any error on this new query...
I ended up solving this with and aditional NOT CONTAINS in the WHERE
clause:
SELECT *
FROM Fotos INNER JOIN CONTAINSTABLE(Fotos,*, 'Simon AND NOT clinic')
AS KEY_TBL0 ON Fotos.Cod = KEY_TBL0.[KEY]
INNER JOIN CONTAINSTABLE(Fotos,*, 'Jane AND NOT clinic') AS
KEY_TBL1 ON Fotos.Cod = KEY_TBL1.[KEY]
WHERE KEY_TBL0.Rank>0 AND KEY_TBL1.Rank>0 AND NOT
CONTAINS(Fotos.*,'clinic')
ORDER BY KEY_TBL0.Rank+KEY_TBL1.Rank DESC, ISNULL(Fotos.FotoDate,
Fotos.CreatedDate) DESC
There is no other way to , but I am concerned with performance.
Thanks for your help.
All the best,
JOEL
On Aug 9, 3:36 pm, "Daniel Crichton" <msn...@.worldofspack.com> wrote:
> joel wrote on Thu, 09 Aug 2007 07:13:00 -0700:
>
>
>
> By using a LEFT JOIN you've told the processor to include all the rows where
> 'Simon AND NOT clinic' is true, whether or not there is a match to them in
> 'Jane AND NOT clinic'. Assuming you have only two indexed columns (because
> you're only doing two CONTAINSTABLE clauses), this means that if a row has
> "Simon" in col1, and "Jane clinic" in col2, then you'll still get a result
> because the first clause finds a row and you're not filtering out failures
> in the second clause.
> If you use an INNER JOIN you'll only get the rows where both conditions are
> true. However, if there is a possibility of "clinic" occurring in a 3rd
> column then you'll need to add another CONTAINSTABLE clause to filter those
> out.
> An easier solution might to be create a new column that has the contents of
> all of the indexed varchar columns concatentated together and index that -
> they you need only one clause. I do this myself for my own sites where I
> want to create a simple query over a number of indexed columns, so I have an
> extra column with all the words from those others dumped into it. Makes life
> a bit simpler
> Dan- Hide quoted text -
> - Show quoted text -
Posted by justifylzxw at 6:00 AM 0 comments
Labels: , , , , , , , , , , , , , , , , ,
Subscribe to: Posts (Atom)