SQL help for a newb

[faust9]

Ok, so I have a normalized DB. One table(links) has a source column and a destination column both of which are FK'ed to a locations table. I have product moving between fixed locations so I'd prefer one table with the locations.

I'm trying to querie the DB and list a full order that shows the correct source and destinations. Here's what I've tried thus far:

transport_tracker=# create table locations(
transport_tracker(# locationID serial primary key not null,
transport_tracker(# location varchar(25));

.
.
.

transport_tracker=# create table links(
transport_tracker(# linkID serial primary key not null,
transport_tracker(# source int references locations(locationID),
transport_tracker(# destination int references locations(locationID));

.
.
.

transport_tracker=# select locations.location as sources, locations.location as destinations
transport_tracker-# from links, locations, locations as loc2
transport_tracker-# where locations.locationid = links.source
transport_tracker-# and loc2.locationid = links.destination;

and this is what I get:

       sources       |     destinations
---------------------+---------------------
 texas               | texas
 washington          | washington
 guam                | guam
(3 rows)

What I 'should get' however is this:

       sources       |     destinations
---------------------+---------------------
 texas               | paris
 washington          | tokyo
 guam                | michigan
(3 rows)

I edited the above manually to show what I should get...

My basic question is how do I write a querie that pulls different pieces of information from one table foreign keyed two two columns of another table? Or, to put another way, how do I write a querie to pull my source and destination information from my locations table?

Thanks a lot.

 

[IRstuff]

This question is really more appropriate for Tek-Tips.com

TTFN

FAQ731-376

 

[xwb]

Try

transport_tracker=# select locations.location as sources, loc2.location as destinations

 

[faust9]

Worked great xwb. Thanks a lot.