CODEWITHSUNDEEP
mysql
Ashok Gujjar
Ashok Gujjar

Reputation: 441

How to combine two tables in MySQL which have only one common column and other are different?

I have two tables,

Table1:

 Date  | Amt | Name
====================
2/5/15 | 100 | abc
--------------------
3/5/15 | 150 | xyz
--------------------
3/5/15 | 170 | pqr
--------------------
5/5/15 | 190 | zzz
--------------------

Table2:

 Date  | Amt1 | Name1
====================
2/5/15 | 130 | www
--------------------
3/5/15 | 110 | eee
--------------------
5/5/15 | 180 | rrr
--------------------

My output Should be:

 Date  | Amt | Name | amt1 | Name1
=====================================
2/5/15 | 100 | abc  | 130  | www
-------------------------------------
3/5/15 | 150 | xyz  | 110  | eee
-------------------------------------
3/5/15 | 170 | pqr  |   -  | -  
-------------------------------------
4/5/15 | 190 | zzz  |   -  | -
-------------------------------------
5/5/15 | -   | -    | 180  | rrr
-------------------------------------

Here Date is common field, So how can i fulfill this using MySQL query? I have heard about FULL OUTER JOIN....But how it is implement in MySQL?

Upvotes: 3

Views: 1215

Answers (3)

Rigel1121
Rigel1121

Reputation: 2016

Do the LEFT JOIN and RIGHT JOIN, then combine them using UNION ALL. See my query below:

SELECT A.Date,Amt,Name,Amt1,Name1 FROM Table1 A LEFT JOIN Table2 B ON A.Date=B.Date
UNION ALL
SELECT B.Date,Amt,Name,Amt1,Name1 FROM Table1 A RIGHT JOIN Table2 B ON A.Date=B.Date

Upvotes: 2

Zhenyi Luo
Zhenyi Luo

Reputation: 61

Use outer join would work for your case:

SELECT column_name(s)
FROM table1
FULL OUTER JOIN table2
ON table1.column_name=table2.column_name;

Upvotes: 0

Bhavya Shaktawat
Bhavya Shaktawat

Reputation: 2512

You can use JOIN to combine two table to get this result

Try this

SELECT *
FROM table1 
FULL OUTER JOIN table2
ON table1.Date=table2.Date;

Upvotes: 0

Related Questions