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
