Re: SQL question

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