MySQL query about union

Question

hi

    table A
    =======
    Name  |  Qty
    ------------
    AA    |  1
    BB    |  2
    CC    |  3
    DD    |  4


    table B
    =======
    Name  |  Qty
    ------------
    AA    |  0
    BB    |  0
    EE    |  0
    ZZ    |  0

and i need this as resoult

    Name  |  Qty
    ------------
    AA    |  1
    BB    |  2
    CC    |  3
    DD    |  4
    EE    |  0
    ZZ    |  0

how to do it ? i try with union but not get the resault

Answer 1

Why do you want the result? Why do you select the value return? I am going to assume that you simply want a union of these queries and then a sum of the qty across both tables:

with cte as (
SELECT Name, Qty
from [Table A]
union all 
SELECT Name, Qty
from [Table B]
)
select Name, SUM(QTY) As QTY 
from cte
GROUP BY NAME;

---

Russel Loski, MCSE Data Platform/Business Intelligence Twitter: @sqlmovers; blog: www.sqlmovers.com

Answer 2

create table tableA (name varchar(30), Qty int)

Insert into tableA values('AA',1),('BB',2),('CC',3),('DD',4)  
create table tableB (name varchar(30), Qty int)
 Insert into tableB values('AA',0),('BB',0),('EE',0),('ZZ',0) 


 Select Coalesce(a.name,b.name) as name, Coalesce(a.Qty,b.Qty) as Qty from tablea a full outer join tableb b on a.name=b.name
 --or
  Select Coalesce(a.name,b.name) as name,  Coalesce(a.Qty,0)+Coalesce(b.Qty,0) as Qty 
  from tablea a full outer join tableb b on a.name=b.name



 drop table tablea,tableb
    

Answer 3

You specifically mentioned MySQL - this is a forum for SQL Server.  Different vendors support different SQL dialects; any suggestions you get here may not work in MySQL.