From: Nathan Young <nyoung@asis.com>
To: pgsql-sql@postgresql.org
Subject: Re: join question
Date: Wed, 27 Nov 2002 09:37:07 -0800
Message-ID: <98FCPJ2YA0B9EDSOVU5ZURIFVR76US2X.3de502c3@inky> (raw)
In-Reply-To: <20021126080514.C77510-100000@megazone23.bigpanda.com>
OK, that works great, but I was told that I should avoid sub-selects when
possible for performance reasons. Also, I used so much mental energy trying
to find a solution that would do either task using a join that I would be
very curious if anyone had a solution.
The setup:
>> 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.
The problem:
>> I would like to be able to get a list of members who have not paid for a
>> given year.
Two possible solutions, both using sub-selects:
> 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;
In addition to my interest in finding a join that could do that, I'm curios
about a couple other things.
My understanding is that exists is optimized so that the first version would
be faster than the second.
"using (memberID)" would be the same as "on member.memberID =
payment.memberID", right?
Thanks!
------------->Nathan
11/26/2002 8:11:53 AM, Stephan Szabo <sszabo@megazone23.bigpanda.com> wrote:
>
>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;
>
>
>
>
---
(([^/]+)/([^/]+)){0,1}/*(([^/]+)/([^/]+)){0,1}/*(([^/]+)/([^/]+)){0,1}/*
(([^/]+)/([^/]+)){0,1}/*(([^/]+)/([^/]+)){0,1}/*(([^/]+)/([^/]+)){0,1}/*
---
Nathan Young
N. C. Young Design
(530)629-4176
http://ncyoung.com
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: nyoung@asis.com
Subject: Re: join question
In-Reply-To: <98FCPJ2YA0B9EDSOVU5ZURIFVR76US2X.3de502c3@inky>
* 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