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
