See interleaved comments below.
> Subject: solution for opposite of this join / join from this subselect
> To: [EMAIL PROTECTED]
> From: "Josh L Bernardini" <[EMAIL PROTECTED]>
> Date: Wed, 22 Jan 2003 11:23:44 -0800
> 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;
It can. How about:
mysql> SELECT lastname, firstname, title, event
-> FROM events e
-> LEFT JOIN (people p
-> LEFT JOIN epeople ep on p.id = ep.pid)
-> ON e.id = ep.eid
-> WHERE ep.pid IS NULL
-> and ep.eid is null
-> and e.id=2
-> ORDER BY e.id;
This appears to work in MySQL 4.0.9; Mysql 3.23 didn't allow the
parentheses.
In fact, 4.0.9 appears to work without the parentheses, but 3.23 still
doesn't.
The point is, you want all of the people and all of the events, so you have
to have people on the left side of a left join and events on the left side
of a left join.
[...]
> 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?
No, it's not so that ep.eid == e.id.
In the result of the join, before applying the where clause, ep.eid is null
and e.id is not null for a row corresponding to an event which has no
associated people.
> 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
