sql - Union three tables and show where data came from (extended) -

there 3 tables each column of names, example

• table 1 - column name 'name' - values 'a', 'b' , 'c'
• table 2 - column name 'name' - values 'a' , 'b'
• table 3 - column nane 'name' - values 'a' , 'c'

the goal union tables - each value of 3 tables should shown 1 time. in addition there should 3 new "virtual columns" showing in table value included('1' when value included, '0' if not). result should this:

  name| table1 | table2 | table3     |    1   |    1   |    1   b   |    1   |    1   |    0   c   |    1   |    0   |    1 

working solution is:

select name, max(table1) table1, max(table2) table2, max(table3) table3 (select name, 1 table1, 0 table2, 0 table3       table1       union       select name, 0 table1, 1 table2, 0 table3       table2       union       select name, 0 table1, 0 table2, 1 table3       table3      ) t group name; 

now there should 1 more column let's in table 1 called 'company', example

• table 1 - column name 'name' - values 'a', 'b' , 'c', column name 'company' - values 'xyz', '', 'zyx'
• table 2 - column name 'name' - values 'a' , 'b'
• table 3 - column nane 'name' - values 'a' , 'c'

desired result is:

name | table1 | table2 | table3 | company    | 1 | 1 | 1 | xyz    b | 1 | 1 | 0 | ''    c | 1 | 0 | 1 | zyx 

i don't succeed...

just add each subquery , outer query:

select name, max(table1) table1, max(table2) table2, max(table3) table3,        max(company) company (select name, 1 table1, 0 table2, 0 table3, company       table1       union       select name, 0 table1, 1 table2, 0 table3, null company       table2       union       select name, 0 table1, 0 table2, 1 table3, null company       table3      ) t group name; 

note: assumes company has 1 value each name.