Different 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
"Meelis Lilbok" <meelis.lilbok@.deltmar.ee> kirjutas snumis news:
us8CkB1PIHA.3676@.TK2MSFTNGP06.phx.gbl...
> 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
>
|||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
http://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
"Meelis Lilbok" <meelis.lilbok@.deltmar.ee> wrote in message
news:us8CkB1PIHA.3676@.TK2MSFTNGP06.phx.gbl...
> 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
>
|||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
"Hilary Cotter" <hilary.cotter@.gmail.com> kirjutas snumis news:
#l4aqEkQIHA.5160@.TK2MSFTNGP05.phx.gbl...
> 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
> --
> http://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
> "Meelis Lilbok" <meelis.lilbok@.deltmar.ee> wrote in message
> news:us8CkB1PIHA.3676@.TK2MSFTNGP06.phx.gbl...
>
|||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
[vbcol=seagreen]
> "Hilary Cotter" <hilary.cotter@.gmail.com> kirjutas snumis news:
> #l4aqEkQIHA.5160@.TK2MSFTNGP05.phx.gbl...
[vbcol=seagreen]
[vbcol=seagreen]
[vbcol=seagreen]
[vbcol=seagreen]
[vbcol=seagreen]
[vbcol=seagreen]
[vbcol=seagreen]
[vbcol=seagreen]
[vbcol=seagreen]
[vbcol=seagreen]