SQL Oracle: add column from other table twice but with reference to different tables

user_17624065 0
Viewed 25

I have 3 tables. One of those tables has a key (unique Number) and out of this table I need a column ("Number-Text") to to be added twice in my output table.

Table1:

BelNo Plant ProductNo
123 A 999
234 A 888
345 B 989
456 A 999

Table2:

BelNo MaterialNo
123 001
234 002
345 001

Table3 (with unique values -> each "No" is unique / no duplicates):

No Number-Text
001 Wood
002 Metal
888 Chair
999 Bed

What I try to get is following table:

BelNo Plant ProductNo Number-Text MaterialNo Number-Text
123 A 999 Bed 001 Wood
234 A 888 Chair 001 Wood
345 B 989 Wardrobe 002 Metal
456 A 999 Bed 001 Wood

My Problem is, that with my current code "Number-Text" shows in both columns always the Text from ProductNo ("Bed", "Chair", ...). What needs to be done, to get a reference in the second "Number-Text" to the column "MaterialNo"?

Here is my current code:

SELECT

Table1.BelNo,
Table1.Plant,
Table1.ProductNo,
Table3.Number-Text,
Table2.MaterialNo,
Table3.Number-Text

FROM
Table1 

LEFT JOIN Table3  ON Table1.ProductNo = Table3.No AND Table3.Language = 'E'

LEFT JOIN Table2  ON Table1.BelNo =Table2.BelNo

WHERE
Table1.Plant = 'A'

Sorry, it is my first post. Hope the problem is clearly defined.

sql
1 Answers

This is where table aliases come in.

To join on a table twice, you give at least the second occurrence an alias. I tend to give both occurrences aliases...

SELECT
    Table1.BelNo,
    Table1.Plant,
    Table1.ProductNo,
    product.Number-Text   AS product_text,
    Table2.MaterialNo,
    material.Number-Text  AS material_text
FROM
    Table1 
LEFT JOIN
    Table3  product
        ON  product.No       = Table1.ProductNo
        AND product.Language = 'E'
LEFT JOIN
    Table2
        ON  Table2.BelNo = Table1.BelNo
LEFT JOIN
    Table3  material
        ON  material.No = Table2.MaterialNo
WHERE
    Table1.Plant = 'A'
Dems_533410
Related