Merge 2 different mysql tables

Question

I have two tables:

Table a:
+----+------+
| id | data |
+----+------+
|  1 | 450  |
|  2 | 500  |
|  3 | 550  |
|  4 | 600  |
|  5 | 650  |
+----+------+

Table b:
+----+------+------+
| id | a_id | note |
+----+------+------+
|  1 |    2 |   25 |
|  2 |    5 |   10 |
+----+------+------+

I need a query that returns a table that consists of every row from table a with the notes from table b. I want 0 filled in where a note isn't available on a row. I want it to look like this:

+----+------+------+
| id | data | note |
+----+------+------+
|  1 | 450  |    0 |
|  2 | 500  |   25 |     
|  3 | 550  |    0 |
|  4 | 600  |    0 |
|  5 | 650  |   10 |
+----+------+------+

How do I do that?

HINT: use an outer join operation, the join predicate will need to be in the ON clause (not the WHERE clause) to match b.a_id to a.id; in the SELECT list, use an expression such as `IFNULL(b.note,0) AS note`. And add an ORDER BY clause if you want rows returned in a particular order. — spencer7593, May 05 '15 at 21:49

score 1 · Accepted Answer · answered May 05 '15 at 21:50

1

select a.id, a.data, coalesce(b.note, 0) as note
from a
left join b on a.id = b.a_id

answered May 05 '15 at 21:50

juergen d

201,996
37
293
362

score 0 · Answer 2 · edited May 23 '17 at 12:14

What are you looking for is called LEFT/RIGHT JOIN. This question will give you more details about what they are.

Assume you have a query like:

SELECT * FROM a LEFT JOIN b ON some_condition;

Then, its output will contain every row from table a, along with data from table b where the condition is met. For rows where the condition is not met, the columns with data from b will contain null.

Merge 2 different mysql tables

2 Answers2