thx. GROUP BY is anachronism (maybe vestigial is better); I forgot to remove it.
While each tuple (doc_id, kind_id) is unique, I might still need DISTINCT. As shown, there can be combinations of ORs ANDed together, which it seems to me would produce some duplicate rows. If so, then I guess I would like to ORDER BY the row count, as a relevance proxy. >No problem. That looks much better. I still don't understand what the >'group by' is doing. I'm surprised it lets you group by a column that is >not in your select clause, with no column functions (sum, count, etc). > >Its bold to suggests changes to something that works, but, since you are >not using column functions, may I suggest you forget 'group by'. You could >use SELECT DISTINCT if you need to, but you shouldn't be getting >duplicates if your classifications table is kosher (keyed on ( doc_id, >kind_id ) ). A perfectionist would use the same join syntax throughout. >ie. documents INNER JOIN classifications... > >Good luck > >-----Original Message----- >From: Bill Conlon [mailto:[EMAIL PROTECTED] >Sent: 16 July 2003 02:43 >To: [EMAIL PROTECTED] >Subject: RE: Witango-Talk: OT: SQL help > > >Simon, > >thx. It took me a while to fully understand how to construct repeated >inner joins to get the selected documents (which are indeed in a separate >table). The inner join is really essential as the query gets more >involved (realistic), with combinations of ANDS and ORs. > >Here's an actual SQL query that works (and gives a validated correct >answer): > >SELECT documents.id, documents.title, documents.description, >documents.filename >FROM documents, >classifications A INNER JOIN classifications B >ON (A.doc_id= B.doc_id) >INNER JOIN classifications C >ON (A.doc_id= C.doc_id) >WHERE >( >A.doc_id=documents.id >AND A.kind_id IN (8, 9) >AND B.kind_id =12 >AND C.kind_id = 49 >) >GROUP BY A.doc_id > >Next up is to build the query dynamically from my input form data. But >that should be easy, since the form itself is built dynamically. > >Thanks for persisting. Any other suggestions are welcome. > >>I much prefer my solution of joining the table to itself. Did you try it? >>The 'group by' solution is a kludge. It is ugly, and it will fail if you >>have duplicate characteristic records. Who writes tek-tips anyway. >> >>Regards Simon. >> >>-----Original Message----- >>From: Bill Conlon [mailto:[EMAIL PROTECTED] >>Sent: 11 July 2003 17:05 >>To: [EMAIL PROTECTED] >>Subject: Re: Witango-Talk: OT: SQL help >> >> >>Here's what I ended up with, thanks to tek-tips.com: >> >>select document >>from t >> where characteristic in ('a','d') >> group by document >> having count(*) = 2 -- this corresonds to the number >> -- of items in the in clause >> >>Without the having clause, I believe you get an OR instead an AND >> >>BTW, I mis-typed case 3, but fortunately the -esp interpreter switch was >>working. >> >>>Lol, your solution is much more effective than mine! >>> >>>Thanks :) >>> >>>Gauthier >>>----- Original Message ----- >>>From: "Bill Downall" <[EMAIL PROTECTED]> >>>To: <[EMAIL PROTECTED]> >>>Sent: Friday, July 11, 2003 1:41 PM >>>Subject: Re: Witango-Talk: OT: SQL help >>> >>> >>>> Bill, >>>> >>>> SELECT DISTINCT document >>>> FROM table >>>> WHERE characteristic IN (a,b) >>>> >>>> This can be done entirely with search action settings, or with a direct >>>DBMS >>>> action. The tricky part, in either case, is getting your list of >>>characteristics to >>>> be interpreted correctly by the DBMS. I use an array, with array >>>attributes >>>> that make the commas come out correctly. >>>> >>>> By the way, this is an example like many I will show in the presentation I >>>> will be making at the San Diego Coroboree conference, on getting the >>>> most out of your DBMS. >>>> >>>> Bill >>>> >>>> >>>> On Thu, 10 Jul 2003 09:03:13 -0700, Bill Conlon wrote: >>>> >>>> > picked my SQL in the street, so it's kind of rudimentary. Maybe >>>> >someone can point me in the right direction. >>>> >>>> >Here's a table >>>> >>>> >document characteristic >>>> >1 a >>>> >1 b >>>> >1 d >>>> >2 a >>>> >2 d >>>> >2 e >>>> >3 a >>>> >3 b >>>> >>>> >I want to SELECT the document(s) that match all of a set of >>>> >characteristics. >>>> >>>> >Say I want all documents with characteristic a AND b: This should return >>>> >documents 1 and 3 >>>> >>>> >>>> >>>> >>>> >>>> ________________________________________________________________________ >>>> TO UNSUBSCRIBE: Go to http://www.witango.com/maillist.taf >>>> _____________________________________________________________________ >>>> Envie de discuter en "live" avec vos amis ? T�l�charger MSN Messenger >>>> http://www.ifrance.com/_reloc/m la 1�re messagerie instantan�e de France >>> >>>________________________________________________________________________ >>>TO UNSUBSCRIBE: Go to http://www.witango.com/maillist.taf >>> >> >> >>Bill Conlon >> >>To the Point >>345 California Avenue Suite 2 >>Palo Alto, CA 94306 >> >>office: 650.327.2175 >>fax: 650.329.8335 >>mobile: 650.906.9929 >>e-mail: mailto:[EMAIL PROTECTED] >>web: http://www.tothept.com >> >> >>________________________________________________________________________ >>TO UNSUBSCRIBE: Go to http://www.witango.com/maillist.taf >> >> >>******************************************************************** >>This message is intended only for the use of the person(s) ("the intended >>recipient(s)") to whom it is addressed. It may contain information which is >>privileged and confidential within the meaning of applicable law. If you >>are not the intended recipient, please contact the sender as soon as >>possible. The views expressed in this communication may not necessarily >>be the views held by LGCSB (Local Government Computer Services Board). >> >>Any attachments have been checked by a virus scanner and appear to be >>clean. >>Please ensure that you also scan all messages, as LGCSB does not accept >>any liability for contamination or damage to your systems. >>******************************************************************** >> >> >>________________________________________________________________________ >>TO UNSUBSCRIBE: Go to http://www.witango.com/maillist.taf >> > > >Bill Conlon > >To the Point >345 California Avenue Suite 2 >Palo Alto, CA 94306 > >office: 650.327.2175 >fax: 650.329.8335 >mobile: 650.906.9929 >e-mail: mailto:[EMAIL PROTECTED] >web: http://www.tothept.com > > >________________________________________________________________________ >TO UNSUBSCRIBE: Go to http://www.witango.com/maillist.taf > > >******************************************************************** >This message is intended only for the use of the person(s) ("the intended >recipient(s)") to whom it is addressed. It may contain information which is >privileged and confidential within the meaning of applicable law. If you >are not the intended recipient, please contact the sender as soon as >possible. The views expressed in this communication may not necessarily >be the views held by LGCSB (Local Government Computer Services Board). > >Any attachments have been checked by a virus scanner and appear to be >clean. >Please ensure that you also scan all messages, as LGCSB does not accept >any liability for contamination or damage to your systems. >******************************************************************** > > >________________________________________________________________________ >TO UNSUBSCRIBE: Go to http://www.witango.com/maillist.taf > Bill Conlon To the Point 345 California Avenue Suite 2 Palo Alto, CA 94306 office: 650.327.2175 fax: 650.329.8335 mobile: 650.906.9929 e-mail: mailto:[EMAIL PROTECTED] web: http://www.tothept.com ________________________________________________________________________ TO UNSUBSCRIBE: Go to http://www.witango.com/maillist.taf
