Advanced sql query

Question

I have to tables in my database :

table1
column1|column2|column5|column13
harry   marry   stan    kyle

table2
column1|column2|column12|column7
kenny   eric    randy    ike

As you can see there are two columns in each table with the same name and two with the different name, I'd like to join those into one, here is the output I want to achieve

column1|column2|column5|column13|column12|column7
harry   marry   stan    kyle     null     null
kenny   eric    null    null     randy    ike

or

column1|column2|column5|column12|column13|column7
harry   marry   stan    null     kyle     null
kenny   eric    null    randy    null     ike

Is this possible? and how? I've tried something like :

(select t1.column1 from table1 t1 union select t2.column1 from table t2)

But I'm stuck ..

score 6 · Accepted Answer · answered May 11 '11 at 14:50

You should be able to do

SELECT t1.column1, 
       t1.column2, 
       t1.column5, 
       null column12, 
       t1.column13, 
       null column7
  FROM table1 t1
UNION ALL
SELECT t2.column1, 
       t2.column2, 
       null column5, 
       t2.column12, 
       null column13, 
       t2.column7
  FROM table2 t2

If the data type (particularly length) is important, you may want to CAST(null as VARCHAR2(100)) rather than just selecting a NULL in the first query.

score 2 · Answer 2 · edited May 23 '17 at 11:47

2

Check out this previous question. It sounds like you want to do a FULL OUTER JOIN despite the fact that MySQL doesn't support this directly. There is a way to achieve this though.

Full Outer Join in MySQL

edited May 23 '17 at 11:47

Community

1
1

answered May 11 '11 at 14:50

maple_shaft

10,435
6
46
74

score 2 · Answer 3 · answered May 11 '11 at 14:53

select
column1 as column1, 
column2 as column2, 
column5 as column5, 
column13 as column13, 
null as column12, 
null as column7
from T1
union 
select
column1 as column1, 
column2 as column2, 
null as column5, 
null as column13, 
column12 as column12, 
column7 as column7
from t2

score 0 · Answer 4 · edited May 11 '11 at 15:18

it looks like a simple union. but I dont understand what you are trying to do.

But if you ant those results you can try the following. I am not sure what the join between the tables are suppose to be so I made an assumption

Select table1.column1,table1.column2,column5,column13,column12,column7
from 
( Select column1,column2,column5,column13,column12,column7 
from table1
left join table2 on table1.column1 = table2.column1

)
union
(
Select table1.column1,table1.column2,column5,column13,column12,column7
from table2
left join table1 on table1.column1 = table2.column1
)

score 0 · Answer 5 · answered May 11 '11 at 15:04

0

You have to list all the columns you need to output as a result.

select t1.c1, t1.c2, t1.c3, t1.c4, '' ,'' from t1
union
select t2.c1, t2.c2, '','' , t2.c7, t2.c8 from t2

is that work ?

answered May 11 '11 at 15:04

Turbot

5,095
1
22
30

Advanced sql query

5 Answers5