Re: [GENERAL] Simple Question, hard answer

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(at)ludwig(dot)ucl(dot)ac(dot)uk |
+-------------------------+--------------------------------------+