Why do the two queries below return different results?

From: Warren Little <wlittle(at)securitylending(dot)com>
To: pgsql-sql(at)postgresql(dot)org
Subject: Why do the two queries below return different results?
Date: 2003-07-16 20:52:53
Message-ID: 1058388773.5345.8.camel@localhost.localdomain
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

Look at the following two queries.

select casepid, origpid
from virbcase
where date_trunc('day', origdt) >= '2003-07-01'
and date_trunc('day', origdt) <= '2003-07-31'
and origpid in (select pid from party where partyid in ('00339',
'00310',
'00320'))
and not exists (select pid from casecombo where casepid = secondpid)

select casepid, origpid
from virbcase
where date_trunc('day', origdt) >= '2003-07-01'
and date_trunc('day', origdt) <= '2003-07-31'
and origpid in (select pid from party where partyid in ('00339',
'00310',
'00320'))
and casepid not in (select secondpid from casecombo)

The second query is broken and I don't understand why.
Correlating the subquery as is done in the first query fixes it.

--
Warren Little
Senior Vice President
Secondary Marketing
Security Lending Wholesale, LC
www.securitylending.com
Tel: 866-369-7763
Fax: 866-849-8082

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message jtx 2003-07-17 00:52:17 relevance
Previous Message Barry Lind 2003-07-16 17:44:00 Re: column doesn't get calculated - update # 2