In the following example, I want to reference the CONDITION table twice in each record to fetch the name of the condition of an item before and after. How could I do that to get the desired result?
Tony

ITEM
------
ITEM_ID (NUMBER 4, pk)
ITEM_NAME (VC2, 50)
BEFORE_CONDITION_ID (NUMBER 4, fk)
AFTER_CONDITION_ID (NUMBER 4, fk)

CONDITION
-------------
CONDTION_ID (Number 4,pk)
CONDITION_NAME (VC2, 50)


Data:
ITEM
-----------
1|Item 1|2|3
2|Item 2|3|4
3|Item 3|1|2
4|Item 4|2|2

CONDITION
-----------
1|New
2|Used
3|Defective
4|Recycled

Desired Result:
ITEM_NAME|BEFORE_CONDITION|AFTER_CONDITION
--------------------------------------------
Item 1|Used |Defective
Item 2|Defective |Recycled
Item 3|New |Used
Item 4|Used |Used

Please use [code] tags in the future to make your SQL and sample data more readable.

See the help for details: http://www.dbforums.com/misc.php?do=bbcode