CODEWITHSUNDEEP
mysqlsql
kemot90
kemot90

Reputation: 569

Make one column with named same columns after UNION

I have two tables:

Table1
id | col1 | col2 | other_id
---------------------------
1  | val1 | val2 | 2
2  | val3 | val4 | 3

Table2
id | col3 | other_id
--------------------
1  | val5 | 4

And I want to join this two tables in this way:

id | col1 | col2 | col3 | other_id
-----------------------------------
1  | val1 | val2 | NULL | 2
2  | val3 | val4 | NULL | 3
1  | NULL | NULL | val5 | 4

I create this SQL query:

(
SELECT * FROM table1 AS M LEFT JOIN table2 AS D ON M.other_id = D.other_id
)
UNION
(
SELECT * FROM table1 AS M RIGHT JOIN table2 AS D ON M.other_id = D.other_id
)

Result:

id   | col1 | col2 | other_id | id   | col3 | other_id
------------------------------------------------------
1    | val1 | val2 | 2        | NULL | NULL | NULL
2    | val3 | val4 | 3        | NULL | NULL | NULL
NULL | NULL | NULL | NULL     | 1    | val5 | 4

But I get multiple columns with same labels. I don't want to use aliases, just join columns with the same name.

Upvotes: 2

Views: 140

Answers (4)

Himanshu
Himanshu

Reputation: 32612

If you want to achieve this without using alias, you have to use each column manually.

(SELECT M.ID,M.Col1,M.Col2,D.Col3,M.Other_ID 
   FROM table1 AS M 
   LEFT JOIN table2 AS D 
   ON M.other_id = D.other_id)
UNION
(SELECT D.ID,M.Col1,M.Col2,D.Col3,D.Other_ID 
   FROM table1 AS M 
   RIGHT JOIN table2 AS D 
   ON M.other_id = D.other_id);

See this SQLFiddle

Result

| ID |   COL1 |   COL2 |   COL3 | OTHER_ID |
--------------------------------------------
|  1 |   val1 |   val2 | (null) |        2 |
|  2 |   val3 |   val4 | (null) |        3 |
|  1 | (null) | (null) |   val5 |        4 |

Upvotes: 2

Taryn
Taryn

Reputation: 247820

You can use UNION ALL for this and just put placeholders for the columns that do not exist in both tables:

select t1.id, t1.col1, t1.col2, null col3, t1.other_id
from table1 t1
union all
select t2.id, null col1, null col2, t2.col3, t2.other_id
from table2 t2

See SQL Fiddle with Demo

Result:

| ID |   COL1 |   COL2 |   COL3 | OTHER_ID |
--------------------------------------------
|  1 |   val1 |   val2 | (null) |        2 |
|  2 |   val3 |   val4 | (null) |        3 |
|  1 | (null) | (null) |   val5 |        4 |

Upvotes: 2

Grisha Weintraub
Grisha Weintraub

Reputation: 7986

SELECT id, col1, col2, ''  col3, other_id
FROM table1 

UNION

SELECT id, '' col1 , '' col2, col3, other_id 
FROM table2

Upvotes: 1

user1151835
user1151835

Reputation:

Union wants that the tables has the same fields. You can put 'artificial' fields to do that

Upvotes: -1

Related Questions