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.