CODEWITHSUNDEEP
sql
Kawaii-Hachii
Kawaii-Hachii

Reputation: 1061

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:

PRODUCT:

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

Upvotes: 0

Views: 57

Answers (2)

APC
APC

Reputation: 146309

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.

Upvotes: 1

Kerrek SB
Kerrek SB

Reputation: 477444

You can join a table to itself:

SELECT p.itemcode, p.itemname, p.replacedbycode, r.itemname, j.projectname
FROM product as p JOIN product as r ON(p.replacedbycode = r.itemcode)
                  JOIN project as j ON(p.projectcode = j.projectcode);

If you use the convention that replacedbycode is acutally null rather than blank, which is the more idiomatic way to use a database, then you could use COALESCE to print a pretty error message in case there's no replacement item:

SELECT p.itemcode, p.itemname, p.replacedbycode, COALESCE(r.itemname, "no replacement"), j.projectname,
FROM product as p LEFT JOIN product as r ON(p.replacedbycode = r.itemcode)
                  JOIN project as j ON(p.projectcode = j.projectcode);

Note that in the second case I'm using a left outer join to include rows which have no replacement specified.

Coming to think of it, the replacebycode column should have a foreign-key constraint to the itemcode column, in which case you shouldn't even be allowed to put an empty string as a value.

Upvotes: 1

Related Questions