How to join two table with different value using Mysql

Question

I Have two table:
Table 1 name: Buy

Name | Name code | Number |

a 01 25
a 01 25
b 02 75
c 03 100
c 03 25
And table 2 name: Sell

Name | Name code | Number |

b 02 60
b 02 40
c 03 80
c 03 40
d 04 45
d 04 50

So, how to make two tables join like this (Using MYSQL Query):

Name | Name code | Number | Name | Name code | Number |

a 01 50 (SUM) NULL NULL NULL
b 02 75 b 02 100(SUM)
c 03 125(SUM) c 03 120(SUM)
NULL NULL NULL d 04 90(SUM)
When i type (SUM), it mean that value is SUM with 2 rows
Because this is so hard for me, so i need help so much
Please help me, i don't know how to do that. Thanks so much
And also if i need condition like b < 70 or C < 30 etc...

`SELECT table1.name, table1."name code", sum(table1.number) AS number, table2.name, table2."name code", sum(table2.number) AS number FROM table1 LEFT JOIN table2 ON table1.name = table2.name GROUP BY table1.name, table2.name` Or something along these lines, I just wrote it blindly. — kry, Aug 10 '18 at 09:33
Actually, cancel that, that will clear the null rows. You need the full outer join instead: https://stackoverflow.com/questions/4796872/how-to-do-a-full-outer-join-in-mysql — kry, Aug 10 '18 at 09:36

Amadan · Accepted Answer · 2018-08-10T11:28:09.297

0

It's a bit awkward because MySQL does not support full outer joins. EDIT because of bug:

SELECT B.name, B.name_code, B.total, S.name, S.name_code, S.total
FROM (
  SELECT name, name_code, SUM(number) AS total FROM buy GROUP BY name, name_code
) B LEFT JOIN (
  SELECT name, name_code, SUM(number) AS total FROM sell GROUP BY name, name_code
) S ON B.name = S.name
UNION
SELECT NULL, NULL, NULL, name, name_code, SUM(number) AS total FROM sell
WHERE name NOT IN (SELECT name FROM buy)
GROUP BY name, name_code;

edited Aug 10 '18 at 11:28

answered Aug 10 '18 at 09:43

Amadan

191,408
23
240
301

But when i put duplicate value on bot table, the value return are wrong – Jacky Euro Aug 10 '18 at 10:37
Can you post example data where this happens, please? – Amadan Aug 10 '18 at 10:39
This is link to image: [link] https://imgur.com/1edhSmi https://imgur.com/VF7J6IN https://imgur.com/zxQyjzI – Jacky Euro Aug 10 '18 at 11:19
If we use this query: SELECT buy.name, buy.name_code, buy.number, sell.name, sell.name_code, sell.number FROM buy LEFT JOIN sell ON buy.name = sell.name
This is the result
https://imgur.com/sPSzrUV – Jacky Euro Aug 10 '18 at 11:23
I see. Your original data didn't have such an example so I didn't see the bug. How about now? – Amadan Aug 10 '18 at 11:28
Ok thank U so much, it work for me very well ^^. Thank u so much, i'm very happy ^^ – Jacky Euro Aug 10 '18 at 11:58
Hey @Amadan, can U write for me a query with "right join"? not with "union"? – Jacky Euro Aug 10 '18 at 13:41

score 0 · Answer 2 · answered Aug 10 '18 at 10:20

0

SELECT table_1.Name,table_1.Namecode ,SUM(table_1.Number),table_2.Name,

table_2.Namecode,SUM(table_2.Number)

FROM table_1 

FULL OUTER JOIN table_2

ON table_1.name = table_2.name

GROUP BY table_1.name,table_2.name

answered Aug 10 '18 at 10:20

Sheril Dev

173
1
1
7

Unfortunately, MySQL still does not support `FULL OUTER JOIN`, as I wrote in my answer. – Amadan Aug 10 '18 at 10:42

How to join two table with different value using Mysql

Name | Name code | Number |

Name | Name code | Number |

So, how to make two tables join like this (Using MYSQL Query):

Name | Name code | Number | Name | Name code | Number |

2 Answers2