Postgres Pro
Downloads
Home   >   mailing lists

Thread: SQL question - problem with INTERSECT

SQL question - problem with INTERSECT

From
"Keith L. Musser"
Date:
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@idisys.com

Re: SQL question - problem with INTERSECT

From
Igor Roboul
Date:
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

Re: SQL question - problem with INTERSECT

From
"Keith L. Musser"
Date:
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
>

Re: SQL question - problem with INTERSECT

From
Tom Lane
Date:
"Keith L. Musser" <kmusser@idisys.com> writes:
> PGSQL doesn't accept the combination.  It gives me this error message:
> "SELECT / HAVING requires aggregates to be valid".

What PG version are you running?  AFAICT this works in 7.0.2, but
I wouldn't be too surprised that you might see a failure like that
in 6.5 or before.

            regards, tom lane

Re: SQL question - problem with INTERSECT

From
hubert depesz lubaczewski
Date:
On Thu, Nov 02, 2000 at 06:50:07AM -0500, Keith L. Musser wrote:
> > upper(a.word) = 'JIM' or upper(a.word) = 'JONES'
> > upper(s.word) = 'HELLO' or upper(s.word) = 'THERE'
> Interesting possibility.  Unfortunately, the outer select ends up being
> a sequential scan over the entire messages table, where at each row the
> message id is looked up sequentially in the nested query.

did you try my quesry with "upper's" in it?
if so then is your index on a.word or on upper(a.word)?
try making functional indies, and of course vacuum'im tables to make indices
work.
this query *should* work with indices.

depesz

--
hubert depesz lubaczewski
------------------------------------------------------------------------
     najwspanialszą rzeczą jaką dało nam nowoczesne społeczeństwo,
      jest niesamowita wręcz łatwość unikania kontaktów z nim ...