Thread: Simple Question, hard answer

Simple Question, hard answer

From
Matthew
Date:
Is there an easier way to do this?

I have a document table, and a keyword table, there is a many to many
relation ship between the two via a link table.  What I want to do is
select all the documents that have two or more keywords.  That is select
.... where keywords.keyword = 'foo1' and keywords.keyword = 'foo2';  The
problem is that after joining the document table to the link table to
the keywords table there is no row that satisfies the criteria.  This
seems like something that would have to be done a lot so I'm wondering
if there is a simple way to do it.  We have accomplished it with the
following SQL statement

select documents.docid, count(documents.docid) as docidcount,
keywords.keyword from documents, link, keywords where (documents.docid =
link.docid and keywords.keyid = link.keyid) and (keyword = 'foo1' or
keyword = 'foo2' ) group by docid having docidcount > 2;

Is there a more efficient way to execute this query?  Sub selects or
something?

Thanks,



Re: [GENERAL] Simple Question, hard answer

From
"Jonathan A. Zdziarski"
Date:
done
On Thu, 18 Mar 1999, Matthew wrote:

> Is there an easier way to do this?
>
> I have a document table, and a keyword table, there is a many to many
> relation ship between the two via a link table.  What I want to do is
> select all the documents that have two or more keywords.  That is select
> .... where keywords.keyword = 'foo1' and keywords.keyword = 'foo2';  The
> problem is that after joining the document table to the link table to
> the keywords table there is no row that satisfies the criteria.  This
> seems like something that would have to be done a lot so I'm wondering
> if there is a simple way to do it.  We have accomplished it with the
> following SQL statement
>
> select documents.docid, count(documents.docid) as docidcount,
> keywords.keyword from documents, link, keywords where (documents.docid =
> link.docid and keywords.keyid = link.keyid) and (keyword = 'foo1' or
> keyword = 'foo2' ) group by docid having docidcount > 2;
>
> Is there a more efficient way to execute this query?  Sub selects or
> something?
>
> Thanks,
>
>
>

Thank you,

Jonathan A. Zdziarski
Sr. Systems Administrator
Netrail, inc.
888.NET.RAIL x240


Re: [GENERAL] Simple Question, hard answer

From
Stuart Rison
Date:
Hi Jonathan,

I was just curious to know what you had 'done'.  I have experienced
problems similar to that encountered by Matthew and would be interested in
any answers.

Matthew, you can find more about this in both the [SQL] and the [GENERAL]
newsgroups under the thread 'Tricy -to me!- SQL query'.  Basically I'm
trying to do the same as you (but just retrieving data from the linking
table).  If you can't track down the postings, I can forward them to you.
There where an number of interesting suggested solutions to that problem.

regards,

S.

>done
>On Thu, 18 Mar 1999, Matthew wrote:
>
>> Is there an easier way to do this?
>>
>> I have a document table, and a keyword table, there is a many to many
>> relation ship between the two via a link table.  What I want to do is
>> select all the documents that have two or more keywords.  That is select
>> .... where keywords.keyword = 'foo1' and keywords.keyword = 'foo2';  The
>> problem is that after joining the document table to the link table to
>> the keywords table there is no row that satisfies the criteria.  This
>> seems like something that would have to be done a lot so I'm wondering
>> if there is a simple way to do it.  We have accomplished it with the
>> following SQL statement
>>
>> select documents.docid, count(documents.docid) as docidcount,
>> keywords.keyword from documents, link, keywords where (documents.docid =
>> link.docid and keywords.keyid = link.keyid) and (keyword = 'foo1' or
>> keyword = 'foo2' ) group by docid having docidcount > 2;
>>
>> Is there a more efficient way to execute this query?  Sub selects or
>> something?
>>
>> Thanks,
>>
>>
>>
>
>Thank you,
>
>Jonathan A. Zdziarski
>Sr. Systems Administrator
>Netrail, inc.
>888.NET.RAIL x240


+-------------------------+--------------------------------------+
| Stuart Rison            | Ludwig Institute for Cancer Research |
+-------------------------+ 91 Riding House Street               |
| Tel. (0171) 878 4041    | London, W1P 8BT, UNITED KINGDOM.     |
| Fax. (0171) 878 4040    | stuart@ludwig.ucl.ac.uk              |
+-------------------------+--------------------------------------+