I want to combine the following tables with the logic given below using sql select statement:

Table: Plant

Table: Supplier

Table: Material

1. Derived table's formattedID column is a combination of identifier columns present in the Plants, Material, Supplier table.
2. Only rows from the Material table will be considered where column wxyz_Code is = 1.
3. wxyz_Code in Material table is renamed as abcd_Code in the DerivedTable table.
4. Material_ID, Plant_ID, Supplier_ID columns in DerivedTable are populated from the ID column of Material, Plant, Supplier, respectively.
5. PC_ID in DerivedTable is filled from Material's PC_ID column

Resulting Table: DerivedTable

CodePudding user response：

While this code is for postgreSQL, would apply to many SQL backends with slight change (ie: || with ):

select 'Automatically Generated'           as Id,
       m.identifier || '/' || p.identifier as formattedId,
       m.wxyz_code                         as abcd_code,
       m.id                                as Material_Id,
       null                                as Supplier_Id,
       p.Id                                as Plant_id,
       m.pc_id
from Plant p,
     Material m
where m.wxyz_code = 1
union all
select 'Automatically Generated'                                  as Id,
       m.identifier || '/' || p.identifier || '/' || s.Identifier as formattedId,
       m.wxyz_code                                                as abcd_code,
       m.id                                                       as Material_Id,
       s.id                                                       as Supplier_Id,
       p.Id                                                       as Plant_id,
       m.pc_id
from Plant p,
     Supplier s,
     Material m
where m.wxyz_code = 1
order by formattedId;

DBFiddle demo