Re: SQL question - problem with INTERSECT

If I remove the "GROUP BY messages.msgid ...", then the result will be
messages whose subject contains either 'Hello' or 'There' in the
subject, but not necessarily both.

I want messages which have both 'Hello' and 'There' in the subject, and
both 'Jim' and 'Jones' in the author.

(For example, if I needed all of 'Hello', 'There', and 'Now' in the
subject, my first HAVING clause would use a count of 3, while the second
HAVING clause would still use a count of 2, for both 'Jim' and 'Jones'.)
So I cannot remove either having clause without changing the meaning.

What I would really like to know is why INTERSECT does not allow this.
If I understand that, maybe I can figure out how to get what I need.

-----Original Message-----
From: Igor Roboul <igor@raduga.dyndns.org>
To: PGSQL-General <pgsql-general@postgresql.org>
Date: Wednesday, November 01, 2000 12:03 AM
Subject: Re: [GENERAL] SQL question - problem with INTERSECT


>On Mon, Oct 30, 2000 at 07:59:06PM -0500, Keith L. Musser wrote:
>> "(SELECT messages.msgid FROM messages, subject_index WHERE
>> ((subject_index.word='Hello' or subject_index.word='There') and
>> (subject_index.msgid = messages.msgid))
>> GROUP BY messages.msgid HAVING count(messages.msgid)=2)
>> INTERSECT
>> (SELECT messages.msgid FROM messages, author_index WHERE
>> ((author_index.word='Jim' or author_index.word='Jones') and
>> (author_index.msgid = messages.msgid))
>> GROUP BY messages.msgid HAVING count(messages.msgid)=2);"
>Try removing first "GROUP BY messages.msgid HAVING
count(messages.msgid)=2)"
>
>--
>Igor Roboul, Unix System Administrator & Programmer @ sanatorium
"Raduga",
>Sochi, Russia
>http://www.brainbench.com/transcript.jsp?pid=304744
>