Обсуждение: join question

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.

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.

Thanks in advance!

--->Nathan







---
(([^/]+)/([^/]+)){0,1}/*(([^/]+)/([^/]+)){0,1}/*(([^/]+)/([^/]+)){0,1}/*
(([^/]+)/([^/]+)){0,1}/*(([^/]+)/([^/]+)){0,1}/*(([^/]+)/([^/]+)){0,1}/*
---



Nathan Young
N. C. Young Design
(530)629-4176
http://ncyoung.com

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.memberIDand 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 wherepayment.memberID = member.memberID and payment.yearPaid =
1999andnot exists (select * from payment wherepayment.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) whereb.yearPaid is null;
 

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

On Wed, 27 Nov 2002 09:37:07 -0800, Nathan Young <nyoung@asis.com>
wrote:
>OK, that works great, but I was told that I should avoid sub-selects when 
>possible for performance reasons.
>>
>> select member.memberId, member.name from member left outer join
>>  (select * from payment where yearPaid=2002) as a using (memberId) where
>>  yearPaid is null;

Nathan,
if you want a version without a subselect, try
   SELECT m.memberId, m.name   FROM member AS m LEFT OUTER JOIN        payment AS p ON p.yearPaid=2002 AND
m.memberId=p.memberId  WHERE p.memberId IS NULL;
 

though I don't know whether it is faster.

ServusManfred