×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

SQL help for a newb

SQL help for a newb

SQL help for a newb

(OP)
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:

CODE

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:

CODE

       sources       |     destinations
---------------------+---------------------
 texas               | texas
 washington          | washington
 guam                | guam
(3 rows)
What I 'should get' however is this:

CODE

       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.

RE: SQL help for a newb

Try

CODE

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

RE: SQL help for a newb

(OP)
Worked great xwb. Thanks a lot.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members! Already a Member? Login


Resources

eBook - 10 Reasons to Choose CATIA on the Cloud
To compete in today’s fast-paced and competitive market, smaller and newer firms need a powerful platform that will enable them to compete with bigger players, without the heavy investments needed in computer hardware, software and personnel. Download Now
White Paper - Smart Manufacturing for Electronics
This white paper describes a transformative approach to electronics manufacturing made possible by the addition of Mentor Graphics to the Siemens family. It describes a completely digitalized strategy that supports both printed circuit board (PCB) and mechanical design and manufacturing, uniting the entire product lifecycle – from idea and production to customers and back. Download Now

Close Box

Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close