CODEWITHSUNDEEP
mysqlsql
Morpheus
Morpheus

Reputation: 3523

pulling data from two different tables with common row elements

I have the following two tables:

Table a:
name  qty
a     10
b     20
c     30 
d     40

and table b
name qty
a     10
b     20
d     20
e     60.

I want to merge there two tables and create a new table like this

name    qty
 a       20
 b       40
 c       30 
 d       60  
 e       60

The objective is to add the values if there is have the same value in name or else just append the values in table two to table 1.

Upvotes: 0

Views: 24

Answers (2)

TommCatt
TommCatt

Reputation: 5636

To simulate a full outer join, just execute a left outer join (gives all the rows of Table A with all matching rows of Table B or NULL) and a right outer join where Table A is NULL (gives all the rows of Table B that have no match in Table A -- matches are already provided in first query).

In the first query, there will always be a Qty value from Table A with either a Qty value or NULL from Table B. In the second query, there will only be a Qty value from Table B.

See Fiddle results.

select  a.Name, a.Qty + IsNull( b.Qty, 0 ) as Qty
from    @TableA a
left outer join @TableB b
    on  b.Name = a.Name
union all
select  b.Name, b.Qty
from    @TableA a
right outer join @TableB b
    on  b.Name = a.Name
where   a.Name is null;

You may use union or union all with the same results. Since there is less processing required with union all, that's what I chose.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269803

Unfortunately, MySQL does not support full outer join. Here is a method using union all and group by:

select name, sum(qty) as qty
from ((select name, qty from a) union all
      (select name, qty from b)
     ) ab
group by name;

Upvotes: 1

Related Questions