List:General Discussion« Previous MessageNext Message »
From:b Date:September 20 2009 1:28am
Subject:Re: right join troubles
View as plain text  
On 09/19/2009 10:07 AM, Thomas Spahni wrote:
> On Sat, 19 Sep 2009, b wrote:
>
>> I'm trying to select all members who have not registered for an event.
>> I have tables 'members', 'events', and 'events_members', the latter a
>> join table with event_id and member_id columns.
>>
>> The closest I've gotten is with this query:
>>
>> SELECT m.id, m.first_name, m.last_name
>> FROM members AS m
>> RIGHT JOIN events_members AS em ON
>> (em.event_id = 10 AND m.id != em.member_id)
>> ORDER BY m.last_name ASC;
>>
>> This returns an empty set IF there are no records at all in
>> events_members with event_id = 10. But, in that case, I want to
>> receive ALL members.
>>
>> However, if I add a single record with event_id = 10, I then get the
>> expected list of all OTHER members. How can I modify this query so
>> that, when there are 0 registered members for a particular event, I
>> get back all members?
>>
>> Obviously, I could always first check for the existence of the
>> event_id in the join table and, if not found, run the select on the
>> members table. But I doubt that that's the best option.
>
> I think that you need two steps:
>
> CREATE TABLE events_members_tmp
> SELECT * FROM events_members
> WHERE event_id = 10;
>
> SELECT m.id, m.first_name, m.last_name
> FROM members AS m
> LEFT JOIN events_members_tmp AS em ON m.id = em.member_id
> WHERE em.member_id IS NULL
> ORDER BY m.last_name ASC;
>
> Having written this it appears that it could work in just one step as well:
>
> SELECT m.id, m.first_name, m.last_name
> FROM members AS m
> LEFT JOIN events_members AS em
> ON em.event_id = 10 AND m.id = em.member_id
> WHERE em.member_id IS NULL
> ORDER BY m.last_name ASC;

That works great. I was sure that I needed a right, rather than left, 
join. But this makes perfect sense.

Thanks a bunch!

Thread
right join troublesb19 Sep
  • Re: right join troublesThomas Spahni19 Sep
    • Re: right join troublesb20 Sep