Hi all
I have a database with a number of tables with related information. I would
like to do a search across all these tables using AND, OR etc, which means
using CONTAINSTABLE, right?
Using something like:
searchStr = "dog AND cat"
SELECT a FROM parenttable
WHERE parentid IN
(SELECT [KEY] FROM CONTAINSTABLE(childtable1, *, searchStr))
OR parentid IN
(SELECT [KEY] FROM CONTAINSTABLE(childtable2, *, searchStr))
I get some results... The problem is, of course, that this means that both
dog and cat have to be in each child table, but I want to get all parent
table rows where dog AND cat appear ANYWHERE in the child tables (eg cat in
one and dog in the other, or both in one child table and none in the other,
and so on). Is this possible? Any suggestions?
This would be used on a search page on an ASP site. The real tables are more
like grandparent - parent - child, child, child.
Thanks a lot in advance!
Marcus
does the key column correspond to the pk of the parent table?
If so, this should work.
|||Thanks for the reply!
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:3BEBA99D-395A-4186-AAC6-6F22F7FE9E28@.microsoft.com...
> does the key column correspond to the pk of the parent table?
> If so, this should work.
Yes, the key column corresponds to the pk of the parent, but it doesn't
work. I am testing on some records where all the rows of child1 contain
"cat" and all the rows of child2 contain "dog". If I set the search string
to "cat" I get all the parent rows, like I should. Likewise, I get all rows
if I set the search string to "dog", of course. However, if I set the search
string to "cat AND dog" I get no rows.
This is not particularly surprising to me, since each "child query" executed
independently returns zero keys for "cat AND dog". Am I missing something
here? What can I do?
Here's the query again, for readability:
SELECT a FROM parenttable
WHERE parentid IN
(SELECT [KEY] FROM CONTAINSTABLE(childtable1, *, searchStr))
OR parentid IN
(SELECT [KEY] FROM CONTAINSTABLE(childtable2, *, searchStr))
Thank you!
Regards,
Marcus
|||No replies... So I take it this is not possible? Any suggestions on other
solutions?
Thanks,
Marcus
"Marcus" <lumbus@.ludd.luth.se> wrote in message
news:uXlNUDROEHA.2876@.TK2MSFTNGP09.phx.gbl...
> Thanks for the reply!
> "Hilary Cotter" <hilaryk@.att.net> wrote in message
> news:3BEBA99D-395A-4186-AAC6-6F22F7FE9E28@.microsoft.com...
> Yes, the key column corresponds to the pk of the parent, but it doesn't
> work. I am testing on some records where all the rows of child1 contain
> "cat" and all the rows of child2 contain "dog". If I set the search string
> to "cat" I get all the parent rows, like I should. Likewise, I get all
rows
> if I set the search string to "dog", of course. However, if I set the
search
> string to "cat AND dog" I get no rows.
> This is not particularly surprising to me, since each "child query"
executed
> independently returns zero keys for "cat AND dog". Am I missing something
> here? What can I do?
> Here's the query again, for readability:
> SELECT a FROM parenttable
> WHERE parentid IN
> (SELECT [KEY] FROM CONTAINSTABLE(childtable1, *, searchStr))
> OR parentid IN
> (SELECT [KEY] FROM CONTAINSTABLE(childtable2, *, searchStr))
> Thank you!
> Regards,
> Marcus
>
|||Marcus,
I had assumed that from your previous "Thanks for the reply" reply that the
[Primary] key (PK) column corresponds to the PK in the parent table and
therefore is solution was viable for you. Is that not correct? If so, then
could you provide more details on your table structures and the real SQL FTS
query?
Thanks,
John
"Marcus" <lumbus@.ludd.luth.se> wrote in message
news:uBT$PwdPEHA.4036@.TK2MSFTNGP12.phx.gbl...[vbcol=seagreen]
> No replies... So I take it this is not possible? Any suggestions on other
> solutions?
> Thanks,
> Marcus
> "Marcus" <lumbus@.ludd.luth.se> wrote in message
> news:uXlNUDROEHA.2876@.TK2MSFTNGP09.phx.gbl...
string[vbcol=seagreen]
> rows
> search
> executed
something
>
|||John,
Thanks for the reply. Either I'm missing something here or there is some
confusion about what I'm looking for. I'll try to explain this very clearly:
My real tables are pretty complicated and I think they will only confuse. I
have made a test database that looks like this:
TABLE [Parent]:
[ParentID] [int] IDENTITY (1, 1) NOT NULL
[Data] [varchar] (100)
TABLE [Child1]:
[ParentID] [int] NOT NULL ,
[Data] [varchar] (100)
TABLE [Child2]:
[ParentID] [int] NOT NULL ,
[Data] [varchar] (100)
I then insert the following rows:
TABLE [Parent]:
ParentID=1, Data='dummy'
TABLE [Child1]:
ParentID=1, Data='the dog says woof'
TABLE [Child2]:
ParentID=1, Data='the cat says miau'
Now, remember: What I want to do is to search for "dog AND cat" in this
structure, returning the IDs for the parent rows where the words "cat and
"dog" appear anywhere in the tables, for example "cat" in Child1 and "dog"
in Child2.
This query...
SELECT ParentID FROM Parent
WHERE ParentID IN
(SELECT [KEY] FROM CONTAINSTABLE(Child1, *, 'cat AND dog'))
OR ParentID IN
(SELECT [KEY] FROM CONTAINSTABLE(Child2, *, 'cat AND dog'))
...returns nothing. This is the problem I'm asking about.
Just to show that the tables are healthy, this query...
SELECT ParentID FROM Parent
WHERE ParentID IN
(SELECT [KEY] FROM CONTAINSTABLE(Child1, *, 'cat OR dog'))
OR ParentID IN
(SELECT [KEY] FROM CONTAINSTABLE(Child2, *, 'cat OR dog'))
...returns ParentID=1. And these queries...
SELECT [KEY] FROM CONTAINSTABLE(Child1, *, 'cat OR dog')
SELECT [KEY] FROM CONTAINSTABLE(Child2, *, 'cat OR dog')
...return KEY=1.
So the question is whether there is a solution to this or whether I should
give up on using FTS for my search functionality.
Thank you!
Regards,
Marcus
"John Kane" <jt-kane@.comcast.net> wrote in message
news:Op2OC3gPEHA.3524@.TK2MSFTNGP09.phx.gbl...
> Marcus,
> I had assumed that from your previous "Thanks for the reply" reply that
the
> [Primary] key (PK) column corresponds to the PK in the parent table and
> therefore is solution was viable for you. Is that not correct? If so, then
> could you provide more details on your table structures and the real SQL
FTS[vbcol=seagreen]
> query?
> Thanks,
> John
>
> "Marcus" <lumbus@.ludd.luth.se> wrote in message
> news:uBT$PwdPEHA.4036@.TK2MSFTNGP12.phx.gbl...
other[vbcol=seagreen]
doesn't[vbcol=seagreen]
contain
> string
> something
>
| |
没有评论:
发表评论