Re: join from this subselect
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
Re: join from this subselect
On Tue, 2003-01-21 at 15:51, Josh L Bernardini wrote: 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. In this query you're querying events with no attendants (people.id IS NULL) and with event Id 2. Did event 2 have at least an attendant? If so, then the result must be empty. I guess you will need a TEMP table. HTH -- __ / \\ @ __ __@ Adolfo Bello [EMAIL PROTECTED] / // // /\ / \\ // \ // Bello Ingenieria S.A, ICQ: 65910258 / \\ // / \\ / // // / //cel: +58 416 609-6213 /___// // / _/ \__\\ //__/ // fax: +58 212 952-6797 www.bisapi.com //pager: www.tun-tun.com (609-6213) - 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
