1

Let's say I have two tables:

TabA

+-----+----+
|TabID|Send|
|    1|   5|
|    2|   8|
|    3|  12|
|    4|  16|
+-----+----+

TabB

+-----+--------+
|TabID|Recieved|
|    1|      17|
|    4|       3|
|    5|       2|
|    6|       8|
+-----+--------+

And I would like result like this:

+-----+----+--------+
|TabID|Send|Recieved|
|    1|   5|      17|
|    2|   8|       3|
|    3|  12|       0|
|    4|  16|       0|
|    5|   0|       2|
|    6|   0|       8|
+-----+----+--------+

I tried this:

SELECT
COALESCE(A.TabID,B.TabID) as TabID,
COALESCE(A.Send,0) as Send,
COALESCE(B.Recieved,0) as Recieved
FROM
TabA AS A
LEFT JOIN
TabB AS B
ON
B.TabID = A.TabID

But I got this:

+-----+----+--------+
|TabID|Send|Recieved|
|    1|   5|      17|
|    2|   8|       3|
|    3|  12|       0|
|    4|  16|       0|
+-----+----+--------+

I'm sure there is a solution in MySQL doc, but I don't know how is this operation called.

Michael Irigoyen
  • 22,513
  • 17
  • 89
  • 131
HonzaT
  • 33
  • 2
  • try: `select a.TabID as TabID, COALESCE(a.Send, 0) as Send, COALESCE(b.Recieved, 0) as Recieved from tabA as a left join tabB as b on a.TabID = b.TabID UNION select c.TabID as TabID, COALESCE(a.Send, 0) as Send, COALESCE(c.Recieved, 0) as Recieved from tabA as a right join tabB as c on a.TabID = c.TabID;` check this [**demo**](http://sqlfiddle.com/#!9/2c4d2/1) – Grijesh Chauhan Apr 07 '15 at 18:29

0 Answers0