Обсуждение: Simple Question, hard answer
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,
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
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 | +-------------------------+--------------------------------------+