which Join to use - mysql

Question

I have two tables. (Below given are example tables. originally I have 6 tables)

Table cpp;

----------------------
| name    |  spent1  |
----------------------
|john     |   32     |
|Johny    |   2      |
----------------------

Table java

----------------------
| name    |  spent2  |
----------------------
|mary     |   42     |
|Johny    |   2      |
----------------------

Output what I want is like

---------------------------------
| name    |  spent1  |  spent2  |
---------------------------------
|john     |   32     |  null    |
|Johny    |   2      |  2       |
|mary     |   null   |  42      |
---------------------------------

I tried many things and couldn't get the answer. Can someone suggest me a join in mysql to get the desired output.

How many john's can be in your system? Do you want your queries to derive from poor joins or unions? How's that? — Drew, Oct 12 '16 at 11:03
What you are looking for is what is called a `full outer join` which MySQL doesn't support natively. There are ways of working around it as explained in this question: https://stackoverflow.com/questions/4796872/full-outer-join-in-mysql (which possibly could be a duplicate target for your question). — jpw, Oct 12 '16 at 11:07
just for example i gave yaar. those tables really not existing — Pawan, Oct 12 '16 at 11:39

PaulF · Answer 1 · 2016-10-12T11:12:16.910

0

You could achieve that by a union of two queries

select Table1.name as name , Table1.spent as spent1 , Table2.spent as spent2 from Table1
Left JOIN Table2 on Table1.name = Table2.name
union
select Table2.name as name , Table1.spent as spent1 , Table2.spent as spent2 from Table2
Left JOIN Table1 on Table1.name = Table2.name
ORDER BY name

Note that by just specifying UNION with ALL - the duplicate entry for johny is automatically removed.

edited Oct 12 '16 at 11:12

answered Oct 12 '16 at 11:09

PaulF

6,673
2
18
29

for two tables it is fine. but i have 6 tables – Pawan Oct 12 '16 at 11:11
Unfortunately, without a redesign of the schema, there is little option – PaulF Oct 12 '16 at 11:13

PaulF · Answer 2 · 2016-10-12T11:41:00.973

Without redesigning the schema (probably your best option) you could create a temporary table & query that - the following should easily be extended to six tables :

drop temporary table if exists temp;
create temporary table temp ENGINE=MEMORY as (select * from
  (select name, spent1, null as spent2, null as spent3, .... from Table1
   union
   select name, null, spent2, null, .... from Table2
   union
   select name, null, null, spent3, .... from Table3
   .....
  ) a);
select name, sum(spent1), sum(spent2), sum(spent3), ....
from temp
group by name;

which Join to use - mysql

2 Answers2