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

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.

CodePudding user response：

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'