Re: Odd query

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.