multiple joins from same table
->Date: Sat, 27 Mar 93 23:05 PST
->From: tech@mirkwood.hayward.ca.us (Technical Support)
->To: informix-list@rmy.emory.edu
->Subject: <No Subject Supplied>
->
->I realize this is probably a fairly simple query, but our
->database programmer is on vacation for 2 weeks and I'm trying
->to keep the backlog of requests from filling up our system...
->
->We're running ISQL (Standard Engine), and have a database
->which has a number of tables. One of these tables (table1) has
->multiple rows similar to:
->
-> last_name,
-> first_name,
-> eff_date,
-> old_store,
-> old_position,
-> new_store,
-> new_position
->
->Where both "old_position" and "new_position" are a 5 CHAR
->field which can then be referenced into another table within
->this database to get the "full text" version of the job position.
->
->The second table (table2) has only 2 fields similar to:
->
-> job_key,
-> job_title
->
->"job_key" is also a 5 CHAR field, which is where "old_position"
->and "new_position" need to join in order for me to get their
->"job_title" information...
->
->I familiar with doing joins on 2 tables, but how would I reference
->both instances of "job_title" for each row in table1??
->
-> Jerry D. Pierce
-> aka tech@mirkwood.hayward.ca.us
Hello, Jerry,
Aliases come to your rescue! Use something like the following:
SELECT last_name, first_name, eff_date, o.job_title, n.job_title
FROM table1, table2 o, table2 n
WHERE old_position = o.job_key
AND new_position = n.job_key
The aliases 'o' and 'n' help the DB distiguish between the two uses of
the same table for joining. You can use almost any strings for aliases,
but I usually use single letters for brevity and ease of typing.
Regards,
Alan
+------------------------------+---------------------------------------+
| R. Alan Popiel | Internet: alan@den.mmc.com |
| Martin Marietta, LSC | ( Please note: My opinions do not ) |
| P.O. Box 179, M/S 5422 | ( represent official Martin policy. ) |
| Denver, Colorado 80201-0179 | Voice: 303-977-9998 |
+------------------------------+---------------------------------------+