CODEWITHSUNDEEP
mysqlsqldatabase
Shahid Shabbir
Shahid Shabbir

Reputation: 25

Combine 2 mysql tables in third table

I have 3 tables in mySql==> tableA, tableB, tableC

In tableA, I have the following

+---------+----+----+--------+------------+--+
|  Name   | N1 | N2 | Color  |   State    |  |
+---------+----+----+--------+------------+--+
| John    | 60 | 50 | Red    | Newyork    |  |
| Tom     | 70 | 60 | Green  | Kansas     |  |
| Mathew  | 50 | 40 | Blue   | Texas      |  |
| James   | 40 | 30 | Yellow | Texas      |  |
| SSS     | 70 | 60 | Pink   | Washington |  |
+---------+----+----+--------+------------+--+

In tableB, I have the following

+--------+----+----+
| S.Name | N3 | N4 |
+--------+----+----+
| Carl   | 10 | 18 |
| Jason  | 15 |  9 |
| Cindy  | 13 | 12 |
| Tim    |  7 | 18 |
| Pam    | 15 | 14 |
+--------+----+----+

In table C, I want the following 

+---------+----+----+--------+------------+--------+
|  Name   | N1 | N2 | Color  |   State    | S.Name |
+---------+----+----+--------+------------+--------+
| John    | 60 | 50 | Red    | Newyork    |        |
| Tom     | 70 | 60 | Green  | Kansas     |        |
| Mathew  | 50 | 40 | Blue   | Texas      |        |
| James   | 40 | 30 | Yellow | Texas      |        |
| SSS     | 70 | 60 | Pink   | Washington |        |
|         | 10 | 18 |        |            | Carl   |
|         | 15 |  9 |        |            | Jason  |
|         | 13 | 12 |        |            | Cindy  |
|         |  7 | 18 |        |            | Tim    |
|         | 15 | 14 |        |            | Pam    |
+---------+----+----+--------+------------+--------+

Please help me with this sql query. Thanks

Upvotes: 0

Views: 122

Answers (4)

arvinder
arvinder

Reputation: 36

There are two table as mention above When we run the code

Create table tablc as
select name,n1,n2,color,State,' ' as sname FROM tablea
union 
select '' as name,n3,n4,'' as color ,'' as State ,'s.name' as sname from tableb

the new table will createenter image description here

the output will display as table tablc

Upvotes: 1

arvinder
arvinder

Reputation: 36

create table tablc as
select name,n1,n2,color,State,' ' as sname FROM tablea
union 
select '' as name,n3,n4,'' as color ,'' as State ,'s.name' as sname from tableb

Upvotes: 1

Demonyowh
Demonyowh

Reputation: 1672

what you can do is just call it by using union all

select name, n1, n2, color, state, null as 's_name' as recordtype from tablea
union all
select null as 'name', n3 as 'n1', n4 as 'n2', null as 'color', null as 
'state', s_name from tableb

Upvotes: 1

Oto Shavadze
Oto Shavadze

Reputation: 42763

As select result, you can use simple union all:

select Name as Name, N1 as N1, N2 as N2, Color as Color, State as State, null as S_Name from tableA
union all
select null as Name, N3 as N1, N4 as N2,  null as Color,  null as State, S_Name as S_Name from tableB

Upvotes: 3

Related Questions