How can I combine one column from only multiple tables?
I find it difficult to formulate a question, so I will show what I am trying to do.
Table1 (ID, Name, Score)
1 Jane 10
2 Jack 15
3 Jill 12
4 Jane 10
Table2 (ID, Name, Score)
1 John 11
2 Jill 14
3 Jack 16
4 Jake 15
I would like to get the result
Result (Name, Table1.Score, Table2.Score)
Jane 20 NULL
Jack 15 16
Jill 12 14
John NULL 11
Jake NULL 15
Can you name the primary key? Note that Jane appears twice in table 1 (may also appear in table 2), and I want to summarize the score and make the name unique in this table.
source to share
Here's one way to do it with UNION
and SUM
:
SELECT T.Name, Sum(T.Score), Sum(T2.Score) Score2
FROM (SELECT Name, SUM(Score) score FROM Table1 GROUP BY Name) t
LEFT JOIN (SELECT Name, SUM(Score) score FROM Table2 GROUP BY Name) t2
ON t.Name = t2.Name
GROUP BY T.Name
UNION
SELECT T.Name, Sum(T2.Score), Sum(T.Score) Score2
FROM (SELECT Name, SUM(Score) score FROM Table2 GROUP BY Name) t
LEFT JOIN (SELECT Name, SUM(Score) score FROM Table1 GROUP BY Name) t2
ON t.Name = t2.Name
GROUP BY T.Name
And here is the SQL Fiddle .
Good luck.
source to share
try it
select t1.name , sum(t1.score) as score1 ,sum(t2.score2) as score2 from Table1 as t1
left JOIN
(select name , score as score2 from Table2 )t2
on t1.name = t2.name
group by name
union all
select t2.name , sum(t1.score1) as score1, sum(t2.score) as score2 from Table2 as t2
left JOIN
(select name , score as score1 from Table1 )t1
on t2.name = t1.name
where score1 is null
group by name
---- if you want use the order add this "order by name" here in the end
and this will output:
NAME | SCORE1 |SCORE2
Jack | 15 | 16
Jane | 20 |(null)
Jill | 12 | 14
Jake | (null) | 15
John | (null) | 11
source to share
Well, if MySQL had full outer join
, you would use that. Here's an alternative method:
select id, name, MAX(score1) as score1, MAX(score2) as score2
from ((select id, name, score as score1, NULL as score2
from table1
) union all
(select id, name, null as score1, score as score2
from table1
)
) t
group by id, name
The idea is to get all the information you need from each table with scores in different columns, using union all
and breaking the score into two separate columns.
The final one group by
concatenates them with one line by one id.
source to share
Try the following:
select
name = Table1.name,
sum(Table1.score) as score1,
sum(Table2.Score) as score2
from Table1
full outer join
Table2
ON
Table2.name = Table1.name
where Table1.name is not null
group by table1.name
UNION
select
name = Table2.name,
sum(Table1.score) as score1,
sum(Table2.Score) as score2
from Table1
full outer join
Table2
ON
Table2.name = Table1.name
where Table2.name is not null
group by table2.name
order by score1 desc, score2
source to share