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
--------------------------------------------------------