Thread: 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.