right join troubles

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.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: right join troubles

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;

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: right join troubles

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!


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org