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=arch...@jab.org
