Re: join question

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;