October 11, 2005 at 8:11 pm
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.
October 11, 2005 at 8:58 pm
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
October 12, 2005 at 12:52 am
Actually, using a @variable does work (from BOL title "CONTAINS")
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, s
ee 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
October 12, 2005 at 4:57 am
"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
October 12, 2005 at 8:54 am
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
October 12, 2005 at 5:20 pm
... 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
October 12, 2005 at 5:36 pm
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.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply