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
