pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Relating 1 table to another.
3+ messages / 3 participants
[nested] [flat]

* Relating 1 table to another.
@ 1999-04-25 08:10 Nigel Tamplin <adt09@dial.pipex.com>
  1999-04-25 16:47 ` Re: [SQL] Relating 1 table to another. Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 3+ messages in thread

From: Nigel Tamplin @ 1999-04-25 08:10 UTC (permalink / raw)
  To: pgsql-sql

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@dial.pipex.com


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




^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: [SQL] Relating 1 table to another.
  1999-04-25 08:10 Relating 1 table to another. Nigel Tamplin <adt09@dial.pipex.com>
@ 1999-04-25 16:47 ` Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 3+ messages in thread

From: Tom Lane @ 1999-04-25 16:47 UTC (permalink / raw)
  To: adt09@dial.pipex.com; +Cc: pgsql-sql

"Nigel Tamplin" <adt09@dial.pipex.com> writes:
> 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;
> 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.

This is where you need table aliases (AS clauses).  You do it like this:

select project.id, project.name, p1.name, p2.name
from project, people as p1, people as p2 where
project.mainpersonid = p1.id and project.standbypersonid = p2.id;

p1 and p2 are now independent sources of tuples --- the fact that
they're both scanning the same table is no problem.

What you'd probably really write is

select project.id, project.name,
       p1.name as mainperson, p2.name as standbyperson
etc...

so that you get helpful column titles instead of just "name" twice.

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: [SQL] Relating 1 table to another.
@ 1999-04-29 11:00 Herouth Maoz <herouth@oumail.openu.ac.il>
  0 siblings, 0 replies; 3+ messages in thread

From: Herouth Maoz @ 1999-04-29 11:00 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; adt09@dial.pipex.com; +Cc: pgsql-sql

At 19:47 +0300 on 25/04/1999, Tom Lane wrote:


>
> This is where you need table aliases (AS clauses).  You do it like this:
>
> select project.id, project.name, p1.name, p2.name
> from project, people as p1, people as p2 where
> project.mainpersonid = p1.id and project.standbypersonid = p2.id;

No, AS is for field aliasing, not table aliasing.

The proper syntax is

SELECT project.id, project.name, p1.name, p2.name
FROM project, people p1, people p2
WHERE project.mainpersonid = p1.id
  AND project.standbypersonid = p2.id;

Herouth

--
Herouth Maoz, Internet developer.
Open University of Israel - Telem project
http://telem.openu.ac.il/~herutma





^ permalink  raw  reply  [nested|flat] 3+ messages in thread


end of thread, other threads:[~1999-04-29 11:00 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1999-04-25 08:10 Relating 1 table to another. Nigel Tamplin <adt09@dial.pipex.com>
1999-04-25 16:47 ` Tom Lane <tgl@sss.pgh.pa.us>
1999-04-29 11:00 Re: [SQL] Relating 1 table to another. Herouth Maoz <herouth@oumail.openu.ac.il>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox