CODEWITHSUNDEEP
mysqlsqljoin
user2666633
user2666633

Reputation: 340

SQL to group a similar table by name

I have two similar tables

Table 1

  | id | name | amount|
  | 2  | Mike | 1000  |
  | 3  | Dave | 2500  |

Table 2

  | id | name | amount|
  | 2  | Mike | 1200  |
  | 4  | James| 2500  |

I want to query the tables to get a result like this:

  | id | name | amount_table1| amount_table2|
  | 2  | Mike | 1000         | 1200         |
  | 3  | Dave | 2500         |              |
  | 4  | james|              | 2500         |

Upvotes: 0

Views: 151

Answers (3)

LukStorms
LukStorms

Reputation: 29677

MySql doesn't support FULL OUTER JOIN.
But it supports LEFT & RIGHT joins and UNION. 

select 
t1.id, t1.name, t1.amount as amount_table1, t2.amount as amount_table2
from Table1 t1
left join Table2 t2 on t1.id = t2.id

union all

select t2.id, t2.name, t1.amount, t2.amount
from Table2 t2
left join Table1 t1 on t2.id = t1.id
where t1.id is null

The first select will get those only in Table1 and those in both.
The second select will get those only in Table2.
And the UNION glues those resultsets together.

If this were for a database that supports FULL JOIN then it would be simplified to:

select 
coalesce(t1.id, t2.id) as id, 
coalesce(t1.name, t2.name) as name, 
t1.amount as amount_table1, 
t2.amount as amount_table2
from Table1 t1
full join Table2 t2 on t1.id = t2.id

Upvotes: 0

jarlh
jarlh

Reputation: 44795

UNION ALL the tables. Do GROUP BY to get one row per id/name combo.

select id, name, sum(amount1), sum(amount2)
from
(
    select id, name, amount as amount1, null as amount2 from table1
    union all
    select id, name, null, amount  from table2
) dt
group by id, name

Upvotes: 2

Rams
Rams

Reputation: 2159

You need to do union with left and right join

select a.id , a.name , a.amount amount_table1,b.amount amount_table2 from table1 a left join table2 b on (a.id=b.id)
union
select b.id , b.name ,a.amount,b.amount from table1 a right join table2 b on (a.id=b.id)

Upvotes: 1

Related Questions