You might try this:
SELECT I.iid, CONCAT(ECOMP.last, ', ', ECOMP.first) AS 'Completed By',
CONCAT(ESUB.last, ', ', ESUB.first) AS 'Submitted By',
FROM idea I JOIN employee ECOMP ON I.completed_by = ECOMP.eid
JOIN employee ESUB ON I.submitted_by = ESUB.eid
andy
Hiep Nguyen wrote:
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
--
Andy Wallace - CISData - IDX Slave
AIM: acmwallace [EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
