thanks to brent, bob and M wells for their contributions to this solution
and to m especially who seems to have put in a lot of time and nailed it.
This query returns a list of people not attending a particular event, given
an events table, a people table, and a many-many epeople table between
them. You can modify the where clause to show all the people attending a
particular event, all the events a person isn't/is attending - most of what
you might need in most many - many relationships.
But I still haven't figured out the importance of left joins vs. right
joins. can anyone explain why this statement requires a right join to work?
could it be rewritten to use a left join?
mysql> SELECT lastname, firstname, title, event
-> FROM people p
-> LEFT JOIN epeople ep on p.id = ep.pid
-> right join events e ON e.id = ep.eid
-> WHERE ep.pid IS NULL
-> and ep.eid is null
-> and e.id=2
-> ORDER BY e.id;
I reworte the statement like this and from doing so understand that, OK
epeople as the pivot point between the two tables needs to be on the
outside of the join - really placing it between the two tables. we're
looking for people where there is no record match for epeople.
SELECT lastname, firstname, title, event
FROM events e
LEFT JOIN epeople ep on e.id = ep.eid
right join people p ON p.id = ep.pid
WHERE ep.pid IS NULL
and e.id=2
ORDER BY e.id;
now whats the functional difference between this
SELECT lastname, firstname, title, event
FROM people p
LEFT JOIN epeople ep on p.id = ep.pid
right join events e on ep.eid = e.id
WHERE ep.pid IS NULL
and e.id = 2
ORDER BY ep.eid;
and this
SELECT lastname, firstname, title, event
FROM people p
LEFT JOIN epeople ep on p.id = ep.pid
right join events e on ep.eid = e.id
WHERE ep.pid IS NULL
and ep.eid=2
ORDER BY e.id;
as written the difference is in the and statements but in my result set
ep.eid == e.id == 2 so why can't you use the second statement
interchangably with the first?
thanks for any insights,
jb
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
