Combining free text tables

Question

Post reply

Combining free text tables

Anjali-159814

SSC-Addicted

Points: 435
More actions
October 11, 2005 at 8:11 pm

#90216

Hi,
I have two tables with one to one relationship.
Primary table
ID (Primary key)
Name (varchar)
.......
Secondary table (this table actually consist additional info about the same record)
ID (foreign key of Primary table)
Profile (varchar)
........
Not all the records can have additional information (meaning in the primary table can have 40 records and secondary can only have 10).
If a user searches for a word then both primary as well as secondary table needs to be checked and all the results must be shown on the order of the key.
<code>
SELECT
  l.*,
  FROM FREETEXTTABLE(Details, *, @SearchText) AS S
  INNER JOIN Details l
   ON l.ListingID = s.
  LEFT JOIN FREETEXTTABLE(ExtraDetails, *, @SearchText) as SS
   ON l.listingid = ss.
</code>
This does not work. Also how do I get values of key in free text tables?
Any help is greatly appreciated. Thanks in advance.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

philcart SSC-Forever Points: 47796 More actions · Answer 1

Check the syntax in Books Online,

"freetext_string
Is the text to search for in the specified column. Variables cannot be used. "

--------------------
Colt 45 - the original point and click interface

John Kane SSCrazy Points: 2810 More actions · Answer 2

Actually, using a @variable does work (from BOL title "CONTAINS")

H. Use CONTAINS with variables

This example uses a variable instead of a specific search term.

USE pubs
GO
DECLARE @SearchWord varchar(30)
SET @SearchWord ='Moon'
SELECT pr_info FROM pub_info WHERE CONTAINS(pr_info, @SearchWord)

You're query not correct for what you're trying to do, 
see the following blog entry for details:

SQL Server FTS across multiple tables or columns
http://spaces.msn.com/members/jtkane/Blog/cns!1pWDBCiDX1uvH5ATJmNCVLPQ!316.entry

-- Multiple columns from multiple tables
SELECT e.LastName, e.FirstName, e.Title, e.Notes, t.TerritoryID, A.[Rank]
   from Employees AS e, EmployeeTerritories t,
     containstable(Employees, Notes, 'BA') as A 
       where A. = e.EmployeeID and t.EmployeeID = e.EmployeeID
           order by A.[Rank] DESC

Hope that helps!
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/

John T. Kane

philcart SSC-Forever Points: 47796 More actions · Answer 3

"Actually, using a @variable does work "

In the context that the poster is trying to use it, no it doesn't.

Using CONTAINS ( because neither FREETEXTTABLE or CONTAINSTABLE support the use of variables ) the original query might work if it's written like this ??

SELECT
  l.* 
FROM Details l
INNER JOIN (
  SELECT ListingID FROM Details WHERE CONTAINS( *, @SearchText )
) as S
ON l.ListingID = s.[ListingID]
LEFT JOIN (
  SELECT ListingID FROM ExtraDetails WHERE CONTAINS( *, @SearchText )
) as SS
ON l.listingid = ss.[ListingID]

--------------------
Colt 45 - the original point and click interface

John Kane SSCrazy Points: 2810 More actions · Answer 4

In context of what the poster is trying to do, yes @variables DO work with CONTAINSTABLE or FREETEXTTABLE, for example:

declare @SearchStr varchar(8000)

SET @SearchStr = '"green" or "white"'

SELECT FT_TBL.au_lname, FT_TBL.au_fname, KEY_TBL.RANK

FROM authors as FT_TBL INNER JOIN

(SELECT * FROM

CONTAINSTABLE(authors,au_lname, @SearchStr)) AS KEY_TBL

ON FT_TBL.au_id = KEY_TBL.

/* -- returns:

au_lname au_fname RANK

---------------------------------------- -------------------- -----------

Green Marjorie 80

White Johnson 80

(2 row(s) affected)

*/

My point is that the @variables are supported in FTS queries and have been in both SQL 7.0 SP1 and in SQL 2000. This issue is not with the @variables, but with how their FTS query is written. I can't test the their query as I don't have a SQL Server instances accessable at this time, but they should re-write the query to match the above and re-test it.

Regards,

John

SQL Full Text Search Blog

http://spaces.msn.com/members/jtkane/

John T. Kane

philcart SSC-Forever Points: 47796 More actions · Answer 5

... but you're using CONTAINSTABLE and the poster is using FREETEXTTABLE. Aren't these two different things, or are you saying that Books Online is wrong and FREETEXTABLE does support variables?

--------------------
Colt 45 - the original point and click interface

Anjali-159814 SSC-Addicted Points: 435 More actions · Answer 6

Hi all

Thank you very much for your answers.

While I can use the variable and it works fine but the problem arises when the primary table does not contain the search word (only the secondary table contains the search words), I get no results.

If I query only the secondary table I get results. My question is how do I get the correct results? If I use inner join then only records that are in both are retrieved. What I need is all the records which meet the search criteria. I would like to reiterate that the secondary table is an extension of primary table (secondary records contains extra features of records in primary table)

Thanks.