Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2004
    Posts
    7

    Unanswered: Pulling Data Twice From A Reference Table In A Single Record

    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

  2. #2
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Code:
    SELECT i.item_name,
           bc.condition_name as before_condition,
           ac.condition_name as after_condition
    FROM item i
      JOIN condition bc ON i.before_condition_id = bc.condition_id
      JOIN condition ac ON i.after_condition_id = ac.condition_id
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •