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
