Skip site navigation (1) Skip section navigation (2)

Re: SQL question

From: Stephan Szabo <sszabo(at)megazone(dot)bigpanda(dot)com>
To: Cath Lawrence <Cath(dot)Lawrence(at)anu(dot)edu(dot)au>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: SQL question
Date: 2003-11-03 05:29:22
Message-ID: 20031102212639.G96035@megazone.bigpanda.com (view raw or flat)
Thread:
Lists: pgsql-novice
On Mon, 3 Nov 2003, Cath Lawrence wrote:

> Hi there,
>
> Here's a mini-problem I'm fiddling with. I have a table with *two*
> cross-references to the same other table. And I want to make a view (or
> do a query) which uses them both. My problem is that so far I can get
> one out but not both; it's got to be some syntax thing I'm messing up.
>
>
> Existing tables:
> bond
> ----
> id	integer primary key
> res1 references residue
> res2 references residue
> (other stuff)
>
> residue
> -------
> id	integer primary key
> code char(3)
> (other stuff)
>
> Desired outcome:
> nice_human_readable_details_about_bond
> -------------
> bond.id,  bond.res1's code, bond.res2's code, (other details from bond
> table)

I think you want something like (add other columns as necessary)
select bond.id, residue1.code, residue2.code
from bond, residue residue1, residue residue2
where residue1.id=bond.res1 and residue2.id=bond.res2;

You can refer to the same table multiple times if you give them unique
correlation names (residue1 and residue2 in the example).


In response to

Responses

pgsql-novice by date

Next:From: Stephan SzaboDate: 2003-11-03 05:44:18
Subject: Re: SQL question
Previous:From: Cath LawrenceDate: 2003-11-03 04:17:37
Subject: SQL question

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group