I think of the HAVING clause as a where clause on the results of a group by.
Troy -----Original Message----- From: Bill Conlon [mailto:[EMAIL PROTECTED] Sent: Sunday, July 13, 2003 3:52 PM To: [EMAIL PROTECTED] Subject: Re: Witango-Talk: OT: SQL help Thx. I din't mean to imply that the HAVING is equivalent to AND, but my intent was to obtain an AND result. I don't think I can do sub-selects with MySQL3.23, so an alternative is needed. In the case of 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 The GROUP BY gives me a set of rows for each document in the set of characteristics, and the HAVING clause eliminates those documents that didn't match all of the characteristics in the set. >On Fri, 11 Jul 2003 09:04:47 -0700, Bill Conlon wrote: > >>Without the having clause, I believe you get an OR instead an AND > >Bill, > >That's not what happens with the HAVING clause. > >When you do a GROUP BY, you are creating multiple summaries of sets of >rows. While the WHERE clause applies criteria to a row at the beginning >of the process, deciding whether to include a ROW in a calculation or >group, the HAVING clause is applied after the groups are assembled and >calculated, and decides whether to include any particular GROUP in the >final result. > >The HAVING COUNT (*) = 2 has nothing to do with how many items are in >your IN clause, unless it is by coincidence. > >On the other hand, you are right about the IN operator acting as an OR. >It is >the equivalent of saying column = 'a' OR column = 'd'. If what you want is >only those where there are two distinct results, one of which is 'a' and one >of which is 'd', you will need one of the more complex queries that will >find >distinct documents with an a 'a' characteristic where there is also a 'd' >characteristic in the same table for another row with the same document: > >SELECT DISTINCT (document) >FROM table t1 >WHERE characteristic = 'a' >AND EXISTS + > (SELECT document FROM table t2 > WHERE characteristic = 'd' > AND t2.document = t1.document ) > >Bill > > > > > > >________________________________________________________________________ >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 ________________________________________________________________________ TO UNSUBSCRIBE: Go to http://www.witango.com/maillist.taf
