Relating 1 table to another.

This is a multi-part message in MIME format.

----=_-GSKq6?e:0P hQ -4F,87H:QGpY- G ==yN_.-p:Fmi5K0u+1yg,Vud_
Content-Type: text/plain; charset=us-ascii

----=_-GSKq6?e:0P hQ -4F,87H:QGpY- G ==yN_.-p:Fmi5K0u+1yg,Vud_
Content-Type: text/plain; charset=us-ascii;
name=relating_1_table_to_another.txt
Content-Transfer-Encoding: 7bit
Content-Disposition: attachment; filename="relating_1_table_to_another.txt"

Hello,

I have a question which is probably best asked with an example.

First I create 2 tables, one called projects and one called people.

create table people ( id int, name char(15) );
create table project ( id int, name char(15), mainpersonid int,
standbyperonid int );

Certain people can be on numerous projects at the same time, hence the
project table references the people instead of the people table having a
column to define what project they are on.

Then I populate the people table with 3 people.

insert into people (id,name) values (1, 'John');
insert into people (id,name) values (2, 'Peter');
insert into people (id,name) values (3, 'Lisa');

The I create 2 projects.

insert into project (id,name,mainpersonid,standbypersonid) values
(1,'Task 1',1,2);
insert into project (id,name,mainpersonid,standbypersonid) values
(2,'Task 2',3,2);

Now, when I do a select to see all the project table I get

select * from project;
id|name |mainpersonid|standbypersonid
--+--------------+------------+---------------
1|Task 1 | 1| 2
2|Task 2 | 3| 2

This is ok, but to be more useful I want to show the names of the people
so I do a

select project.id, project.name, people.name from project,people where
project.mainpersonid = people.id;
id|name |name
--+--------------+---------------
1|Task 1 |John
2|Task 2 |Lisa

That works but only shows the name of the mainperson, ok I can change it
to show the name of the standby person instead which brings me on to my
question...

How can I show the names of both the main person and standby person for
each project.

Thanks,

Please could any replies be emailed directly to me as well/ instead of
to the list.

Nigel
email: adt09(at)dial(dot)pipex(dot)com

----=_-GSKq6?e:0P hQ -4F,87H:QGpY- G ==yN_.-p:Fmi5K0u+1yg,Vud_--