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",