List:General Discussion« Previous MessageNext Message »
From:Andy Wallace Date:December 5 2007 8:00pm
Subject:Re: help with select
View as plain text  
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@stripped
Thread
help with selectHiep Nguyen5 Dec
  • Re: help with selectMartin Gainty5 Dec
  • Re: help with selectAndy Wallace5 Dec