SQL query: make a report for all products

I need help to build SQL query.

I have 2 tables table PROJECT and PRODUCT, which has this fields:

PROJECT:

• PROJECTCODE
• PROJECTNAME

PRODUCT:

• ITEMCODE
• ITEMNAME
• PROJECTCODE
• REPLACEDBYCODE

The REPLACEDBYCODE will contain other ITEMCODE (meaning, this product has been replaced by other product). REPLACEDBYCODE will have value of blank ” if the product has NOT been replaced yet.

I want to make a report for all products like this:

ITEMCODE | ITEMNAME | REPLACEDBYCODE | **REPLACED_ITEM_NAME** | PROJECTNAME

Answer

You need to join PRODUCT to itself through an outer join. That is, the query will return all items, and displayed the new name for those itmes which have replacement codes.

 select p1.itemcode
        , p1.itemname
        , p1.replacedbycode
        , p2.itemname replaced_item_name
from product p1
       left outer join product p2
          on p1.replacedbycode = p2.itemcode
/

---

Note: this is the ANSI join syntax. Some flavours of RDBMS may have an alternative syntax for outer joins, for instance Oracle pre-9i.