From: Stephan Szabo <sszabo@megazone23.bigpanda.com>
To: nathan@ncyoung.com
Cc: pgsql-sql@postgresql.org
Subject: Re: join question
Date: Tue, 26 Nov 2002 08:11:53 -0800 (PST)
Message-ID: <20021126080514.C77510-100000@megazone23.bigpanda.com> (raw)
In-Reply-To: <MHTPHEXXXSIELH1XSDBVSPOZYE9BA.3dde8914@inky>
On Fri, 22 Nov 2002, Nathan Young wrote:
> Hi all.
>
> I have a table with members and a table with payments. Each payment is
> related to a member by memberID and each payment has (among other things) a
> year paid.
>
> I can create a join to find a list of members who have paid for a given year
> (2002 in this case):
>
> select member.memberID,member.name from member, payment where
> payment.memberID = member.memberID and payment.yearPaid = 2002
>
> I would like to be able to get a list of members who have not paid for a
> given year.
Well, I believe either of these two will do that:
select member.memberId, member.name from member where not exists (select
* from payment where payment.memberId=member.memberID and
payment.yearPaid=2002);
select member.memberId, member.name from member left outer join
(select * from payment where yearPaid=2002) as a using (memberId) where
yearPaid is null;
> I would also like to combine the two criteria, for example to generate a list
> of members who have paid for 2002 but not 2003.
I think these would do that:
select member.memberID,member.name from member, payment where
payment.memberID = member.memberID and payment.yearPaid = 1999
and not exists (select * from payment where
payment.memberId=member.memberId and yearPaid=2002);
select member.memberId, member.name from member inner join (select
* from payment where yearPaid=2002) as a using (memberId) left outer join
(select * from payment where yearPaid=2003) as b using (memberId) where
b.yearPaid is null;
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: sszabo@megazone23.bigpanda.com, nathan@ncyoung.com
Subject: Re: join question
In-Reply-To: <20021126080514.C77510-100000@megazone23.bigpanda.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox