CODEWITHSUNDEEP
sqloracle-database
adel sameer
adel sameer

Reputation: 53

How to Duplicate a column or record when combining multiple table

I don't know if the title describes my requirements. I have a table C_Bpartner (with C_BPartner_ID as a Primary Key) for employees like this:
name Hiringorderno Orderdate C_Bpartner_ID
A 30 25/02/2002 100
B 47 13/10/2005 101
D 110 22/09/2010 105

and other tables like emp_training:
C_Bpartner_ID TrainingOrderno Orderdate
100 46 14/05/2012
100 58 10/07/2013
101 76 22/10/2015

and emp_penalty:
C_Bpartner_ID PenaltyOrderno Orderdate
105 133 14/05/2012
101 153 25/03/2018

I want the resulting table to be like:
name orderno Orderdate C_Bpartner_ID
A 30 25/02/2012 100
A 46 14/05/2005 100
A 58 10/07/2013 100
B 47 13/10/2005 101
B 76 22/10/2015 101
B 153 25/03/2018 101
D 110 22/09/2010 105
D 133 14/05/2012 105

so, I joined C_BPartner with itself and coalesce them, in order to get a second record for the same C_BPartner_ID. then tried to get the Hiringorderno from C_BPartner bp and join C_BPartner pp with emp_penalty pt(as an example) and get PenaltyOrderno and combine them with coalesce(bp.Hiringorderno,pt.PenaltyOrderno) and do that for all other tables and for Orderdate as well. but it doesn't duplicate records. it picks the first coalesce parameter and discards the other. like this 

coalesce(bp.name,pp.name)   coalesce(bp.Hiringorderno,pt.PenaltyOrderno)  Hiringorderno   PenaltyOrderno
          A                                         30                          30              null
          B                                         47                          47              153

the emp_penalty record for B is not there.

Upvotes: 0

Views: 62

Answers (2)

kfinity
kfinity

Reputation: 9091

There's other ways to do this, but I think the most clear and intuitive way is to UNION the 3 queries that you're trying to do.

select name, hiringorderno as orderno, orderdate, C_Bpartner_ID, 'HIRING' as ordertype, null as emp_penalty_ID
    from C_Bpartner
union all
select bp.name, trainingorderno, t.orderdate, bp.C_Bpartner_ID, 'TRAINING', null
    from emp_training t
    join C_Bpartner bp 
        on bp.C_Bpartner_ID = t.C_Bpartner_ID
union all
select bp.name, PenaltyOrderno, p.orderdate, bp.C_Bpartner_ID, 'PENALTY', p.emp_penalty_ID
    from emp_penalty p
    join C_Bpartner bp 
        on bp.C_Bpartner_ID = p.C_Bpartner_ID
;

Edit: I added 2 columns to show 2 common ways to differentiate the union'ed records.

One way is to add a constant string or number to each select statement - that way you can use CASE WHEN ordertype = 'PENALTY' ... or WHERE ordertype = 'TRAINING' to filter your records.

Another way, like you mentioned, is to fill in a column for one of the selects, like emp_penalty_id, but set it to null for the other select statements.

All the select statements being unioned together need to have the same number of columns, with compatible types. The first select statement defines the column names and types for the rest, which is why I didn't need to add column aliases to the second and third selects.

Upvotes: 1

Littlefoot
Littlefoot

Reputation: 142705

One option is to union 3 queries:

SQL> with
  2  c_bpartner (name, hiringorderno, orderdate, c_bpartner_id) as
  3    (select 'A', 30, date '2002-02-25', 100 from dual union all
  4     select 'B', 47, date '2005-10-13', 101 from dual union all
  5     select 'D', 110,date '2010-09-22', 105 from dual
  6    ),
  7  emp_training(c_bpartner_id, trainingorderno, orderdate) as
  8    (select 100, 46, date '2012-05-14' from dual union all
  9     select 100, 58, date '2013-07-10' from dual union all
 10     select 101, 76, date '2015-10-22' from dual
 11    ),
 12  emp_penalty (c_bpartner_id, penaltyorderno, orderdate) as
 13    (select 105, 133, date '2012-05-14' from dual union all
 14     select 101, 153, date '2018-03-25' from dual
 15    )
 16  select c.name, c.hiringorderno as orderno, c.orderdate, c.c_bpartner_id
 17  from c_bpartner c
 18  union all
 19  select c.name, t.trainingorderno, t.orderdate, t.c_bpartner_id
 20  from c_bpartner c join emp_training t on t.c_bpartner_id = c.c_bpartner_id
 21  union all
 22  select c.name, p.penaltyorderno, p.orderdate, p.c_bpartner_id
 23  from c_bpartner c join emp_penalty p on p.c_bpartner_id = c.c_bpartner_id
 24  order by 1, 2;

N    ORDERNO ORDERDATE  C_BPARTNER_ID
- ---------- ---------- -------------
A         30 25/02/2002           100
A         46 14/05/2012           100
A         58 10/07/2013           100
B         47 13/10/2005           101
B         76 22/10/2015           101
B        153 25/03/2018           101
D        110 22/09/2010           105
D        133 14/05/2012           105

8 rows selected.

SQL>

Upvotes: 1

Related Questions