 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
SQL Server Forum / Other Technologies / Full-Text Search / December 2007Different columns
Hi ! Lets say i have a table TITLES with columns ID,INDEKS,TEKST Values in row are ID INDEKS TEKST 1 World War II Day by Day now is use query SELECT * FROM TITLES T INNER JOIN CONTAINSTABLE(TITLES,*,'"war" and "day"') CT ON T.ID=CT.[KEY] This query returns nothing because word "war" is in column INDEKS and word "day" is in column "TEKST"! Is there any solusion? Best regards; Meelis i have tryed different solutions(freetext,freetextable,contains, multible joins aso.) but with no luck The problem is when i use query SELECT * FROM TITLES T INNER JOIN CONTAINSTABLE(TITLES,*,'"war" and "day"') CT ON T.ID=CT.[KEY] I need get result when word "war" is in field INDEKS and word "day" is in field TEKST word "day" is in field INDEKS and word "war" is in field TEKST or word "war" is in field INDEKS and word "day" is in field INDEKS or word "war" is in field TEKST and word "day" is in field TEKST Meelis > Hi ! > [quoted text clipped - 16 lines] > Best regards; > Meelis Does this work for you? create table titles(id int identity not null constraint titlespk primary key,INDEKS varchar(50),TEKST varchar(50)) GO insert into titles(indeks,tekst) values('World War','Day by Day') insert into titles(indeks,tekst) values('World War Day','test') insert into titles(indeks,tekst) values('test','World Way Day') GO create fulltext catalog test as default GO create fulltext index on titles(indeks,tekst) key index titlespk Go select * from titles join containstable(titles,indeks,'War') as a on a.[key]=id join containstable(titles,tekst,'day') as b on titles.id=b.[key] GO  Signaturehttp://www.zetainteractive.com - Shift Happens! 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 > Hi ! > [quoted text clipped - 16 lines] > Best regards; > Meelis Hi Does not, let me explain with my poor english INDEKS column is indexed part for book titles TEKST column is for text. When book title has any non alphanumeric chars then title is splited to two parts for example: Title is "World War II - Day by Day" then indeks=World War II tekst=- Day by Day now user searches words "war" and "day" with containstable i get nothing back, beacuse "war" is on INDEKS and "day" is on TEKST searched words can be on both or only on field! Best regards; Meelis > Does this work for you? > [quoted text clipped - 34 lines] >> Best regards; >> Meelis Taking Hilary's example and expanding it to handle your cases, this might work: select * from titles left join containstable(titles,indeks,'War') as a on a.[key]=id left join containstable(titles,indeks,'day') as b on b.[key]=id left join containstable(titles,tekst,'War') as c on titles.id=c.[key] left join containstable(titles,tekst,'day') as d on titles.id=d.[key] where (a.[key] is not null and (c.[key] is not null or d.[key] is not null)) or (b.[key] is not null and (c.[key] is not null or a.[key] is not null)) or (c.[key] is not null and (b.[key] is not null or d.[key] is not null)) or (d.[key] is not null and (c.[key] is not null or a.[key] is not null)) which is messy and probably not very efficient, and becomes increasingly more complex to construct as you add terms. An alternative is to create a new column that you index for these multiple column searches, concatentating the columns together - this is what I do for wide searches on my own site - eg. if you search in the Title column it only finds words in Title, but if you do a "wide" search it uses the Keywords column which is concatentated from Title + SubTitle + Author + Subject + AdditionalKeywords. Dan Meels wrote on Wed, 19 Dec 2007 16:27:30 +0200: > Hi > Does not, let me explain with my poor english > INDEKS column is indexed part for book titles > TEKST column is for text. > When book title has any non alphanumeric chars then title is splited to > two parts for example: Title is "World War II - Day by Day" > then indeks=World War II tekst=- Day by Day > now user searches words "war" and "day" > with containstable i get nothing back, beacuse "war" is on INDEKS and > "day" is on TEKST > searched words can be on both or only on field! > Best regards; > Meelis >> Does this work for you? >> create table titles(id int identity not null constraint titlespk >> primary key,INDEKS varchar(50),TEKST varchar(50)) [quoted text clipped - 7 lines] >> titles.id=b.[key] >> GO >> Looking for a SQL Server replication book? >> http://www.nwsu.com/0974973602.html >> Looking for a FAQ on Indexing Services/SQL FTS >>> Hi ! >>> Lets say i have a table TITLES with columns ID,INDEKS,TEKST >>> Values in row are >>> ID INDEKS TEKST 1 World War II Day by Day >>> now is use query >>> SELECT * FROM TITLES T INNER JOIN CONTAINSTABLE(TITLES,*,'"war" and >>> "day"') CT >>> ON T.ID=CT.[KEY] >>> This query returns nothing because word "war" is in column INDEKS >>> and word "day" is in column "TEKST"! >>> Is there any solusion? >>> Best regards; >>> Meelis |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.