mysql> select idea.iid, concat(employee.first," ",employee.last),(SELECT concat( employee.first," ",employee.last) FROM EMPLOYEE where employee.eid=idea.submitted_by) from ide a,employee where idea.iid=1 and idea.completed_by=employee.eid;
M-- ----- Original Message ----- From: "Hiep Nguyen" <[EMAIL PROTECTED]> To: <mysql@lists.mysql.com> Sent: Wednesday, December 05, 2007 2:02 PM Subject: help with select > hi list, > > i have two tables: > > idea(iid int not null primary_key auto_increment, > completed_by int, > submitted_by int); > > employee(eid int not null primary_key auto_increment, > first varchar(20), > last varchar(30)); > > table idea data: > 1 | 4 | 10 > 2 | 3 | 7 > > table employee data: > 3 | john | Doe > 4 | betty | smith > 7 | bob | Gomez > 10 | sun | mcnab > > i'm trying to select from idea table such that when iid = 1, i should get > betty smith for completed_by column and sun mcnab for submitted_by column. > > > 1st trial: > select iid,completed_by,submitted_by from idea where iid=1 > > i got: > 1 | 4 | 10 > > 2nd trial: > select idd,concat(first," ",last),submitted_by from idea,employee where > iid=1 and completed_by=eid; > > i got: > 1 | betty smith | 10 > > now, instead of 10 for the submitted_by column, how do i get sun mcnab? > > thanks, > T. Hiep > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
