Create new column with values partially from existing data and rest with a join SQL/BQ-CodePudding

Main Table:

ID	Table_Name
1	tb_1
2	tb_1
3	tb_1
4	tb_2
5	tb_2
6	tb_2

tb_1 :

a_ID	tb1_id
4	44
5	55
6	66

tb_2 :

b_ID	tb2_id
1	11
2	22
3	33

Output:

ID	Table_Name	tb1_id	tb2_id
1	tb_1	1	11
2	tb_1	2	22
3	tb_1	3	33
4	tb_2	44	4
5	tb_2	55	5
6	tb_2	66	6

Is it possible to create the output as mentioned above using the three tables ? For eg : When a new column tb1_id is created in the main table , the values are derived from tb_1 table, in such a way that , if in the main table there elements under ID that are tagged to tb_1 under table_name column then the values in tb1_id will be same as ID column , however for the non tb_1 table, it should be taken from tb_1 table.

CodePudding user response：

Consider below approach

select t.id, t.table_name, 
  ifnull(tb1_id, id) as tb1_id,
  ifnull(tb2_id, id) as tb2_id
from main_table t
left join tb_1 on id = a_id 
left join tb_2 on id = b_id

if applied to sample data in your question - output is

CodePudding user response：

insert into output 
    as select 
        maintable.ID,
        maintable.Table_Name 
        maintable.ID as tb1_id,
        tb_1.a_id as tb2_id
    from tb_1 a
    inner join maintable on tb_1.a_id = maintable.ID
 union
    select 
        maintable.ID,
        maintable.Table_Name 
        tb_2.b_id as tb1_id,
        maintable.ID as tb2_id
    from tb_2 b
    inner join maintable on tb_2.b_id = maintable.ID