SEO

#1 (**permalink**) 02-28-2008, 07:02 AM

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

#2 (**permalink**) 02-28-2008, 07:02 AM

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" <hiep@ee.ucr.edu>
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/mysql?unsub=mgainty@hotmail.com
>
>

#3 (**permalink**) 02-28-2008, 07:02 AM

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

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode