>
I guess you just need to take the rows from the people table, not from the epeople
table, and put the condition about the event id in the join, not the main where. Like
so:
select people.id, concat(lastname, ", ", firstname) as name from people
left join epeople on epeople.pid=people.id
and epeople.eid=2
where people.id is null;
Hope it works.
> -----------
>
> Asunto: join from this subselect
> Fecha: Tue, 21 Jan 2003 11:51:13 -0800
> De: "Josh L Bernardini" <[EMAIL PROTECTED]>
> A: [EMAIL PROTECTED]
>
> 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:
>
> select epeople.pid, concat(lastname, ", ", firstname) as name from epeople
> left join people on epeople.pid=people.id
> where epeople.eid=2
> and people.id is null;
>
> but I get an empty set.
>
> The following lists people attending an event:
>
> select epeople.pid, concat(lastname, ", ", firstname) as name from epeople
> left join people on epeople.pid=people.id
> where epeople.eid=2;
>
> tia,
> jb
>
> PS is 4.1 available anywhere so I could just use the subselect. Under 4.1,
> are subselects or joins more efficient?
>
> ------------------------------------------------------------------------
---------------------------------------------------------------------
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
