Skip site navigation (1) Skip section navigation (2)

SQL question - problem with INTERSECT

From: "Keith L(dot) Musser" <kmusser(at)idisys(dot)com>
To: "PGSQL-General" <pgsql-general(at)postgresql(dot)org>
Cc: "Dave Trepanier" <be164(at)yahoo(dot)com>
Subject: SQL question - problem with INTERSECT
Date: 2000-10-31 00:59:06
Message-ID: 001d01c042d5$c8ca1cd0$0201a8c0@quantum.idisys.com (view raw or flat)
Thread:
Lists: pgsql-general
Hi,

I want to do a complicated SQL query as follows:

"(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);"

The first SELECT is supposed to find messages whose subject contains
both 'Hello' and 'There'.  The second SELECT is supposed to find all
messages in which the author contains both 'Jim' and 'Jones'.  Each work
fine independently, but given me an error when combined with INTERSECT.

PGSQL doesn't accept the combination.  It gives me this error message:
"SELECT / HAVING requires aggregates to be valid".

Can somebody explain what this means, and how I should fix it?

Keith L. Musser
Integrated Dynamics, Inc.
812-371-7777
email:  kmusser(at)idisys(dot)com





Responses

pgsql-general by date

Next:From: Ashley ClarkDate: 2000-10-31 02:36:12
Subject: Re: list configuration
Previous:From: Roderick A. AndersonDate: 2000-10-30 23:25:28
Subject: Chained table entries

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group