Thread: Odd query

Let's say I have a table :

create table people (
person_id integer not null primary key,
firstnames varchar(50),
lastname varchar(50)
);

and another

create table partners (
first_person integer not null references people(person_id),
second_person integer not null reference people(person_id)
);

Let's do some test data:

select setval('people_seq',0);
insert into people (person_id, firstnames, lastname) values
(nextval('people_seq'), 'Prince Of', 'Darkness');
insert into people (person_id, firstnames, lastname) values
(nextval('people_seq'), 'William Henry', 'Gates');
insert into people (person_id, firstnames, lastname) values
(nextval('people_seq'), 'Steve', 'Case');

insert into partners (first_person, second_person) values (1,2);
insert into partners (first_person, second_person) values (1,3);

Now is it possible for me to do a select statement without any
subqueries to get the firstnames and lastname of each member of a
partnership. e.g.

first_firstnames | first_lastname | second_firstnames | second_lastname
-----------------+----------------+-------------------+----------------
Prince Of        | Darkness       | William Henry     | Gates
Prince Of        | Darkness       | Steve             | Case

Thanks for any help anybody can give...

-jag

--
--------------------------------------------------------
Joshua Ginsberg                 rainman@owlnet.rice.edu
Director of Technology          dirtech@sa.rice.edu
Student Association             AIM: L0stInTheDesert
Rice University, Houston, TX    Cellphone: 713.478.1769
========================================================
"Programming today is a race between software engineers
striving to build bigger and better idiot-proof programs
and the Universe trying to produce bigger and better
idiots. So far, the Universe is winning." -Rich Cook
--------------------------------------------------------

On Tue, Sep 25, 2001 at 08:30:37PM -0500, Joshua Adam Ginsberg wrote:
> Let's say I have a table :
>
> create table people (
> person_id integer not null primary key,
> firstnames varchar(50),
> lastname varchar(50)
> );
>
> and another
>
> create table partners (
> first_person integer not null references people(person_id),
> second_person integer not null reference people(person_id)
> );

> first_firstnames | first_lastname | second_firstnames | second_lastname
> -----------------+----------------+-------------------+----------------
> Prince Of        | Darkness       | William Henry     | Gates
> Prince Of        | Darkness       | Steve             | Case

select a.firstnames as first_firstnames, a.lastname as first_lastname,
       b.firstnames as second_firstnames, b.lastname as second_lastname
from people a, people b, partners
where a.person_id = partners.first_person
and b.person_id = partners.second_person;

HTH,
--
Martijn van Oosterhout <kleptog@svana.org>
http://svana.org/kleptog/
> Magnetism, electricity and motion are like a three-for-two special offer:
> if you have two of them, the third one comes free.