Обсуждение: SQL question

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)





Cath Lawrence,                       Cath.Lawrence@anu.edu.au
Senior Scientific Programmer,  Centre for Bioinformation Science,
John Curtin School of Medical Research (room 4088)
Australian National University,  Canberra ACT 0200
ph: (02) 61257959   mobile: 0421-902694   fax: (02) 61252595

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

On Sun, 2 Nov 2003, Stephan Szabo wrote:

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

As a side note, the above is assuming that the res1 and res2 aren't null.
If you want to handle cases where either res1 or res2 or both are null,
you'll want to look at outer joins.