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 awallace@cisdata.net