How about:
select people.id ...
from people left join epeople
on epeople.pid=people.id
and epeople.eid=2
where epeople.pid is null;
The left join gives you:
(1) rows for people who attended event 2, with epeople columns populated
(2) rows for people who did not attend event 2, with nulls in epeople
columns
Then the where restricts to (2).
More generally,
select ... from L left join R on CONDITION
gives you:
(1) the result of the inner join,
select ... from L, R where CONDITION
(2) the rows from L that were not used in (1), with nulls for the R
columns
Then, you can apply an additional WHERE clause to that.
hth
> Subject: join from this subselect
> To: [EMAIL PROTECTED]
> From: "Josh L Bernardini" <[EMAIL PROTECTED]>
> Date: Tue, 21 Jan 2003 11:51:13 -0800
> I can't come up with the join syntax to mimic this subselect query to list
> people not attending an event (*epeople.eid == event.id):
> select people.id as pid, concat(lastname, ", ", firstname) as name from
> people where people.id not in
> (select epeople.pid from
> epeople
> left join people on epeople.pid=people.id
> where epeople.eid=2);
> Thought it would be:
[...]
---------------------------------------------------------------------
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
