CODEWITHSUNDEEP
mysqljoin
Luke
Luke

Reputation: 707

Merge 2 different mysql tables

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?

Upvotes: 1

Views: 23

Answers (2)

juergen d
juergen d

Reputation: 204746

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

Upvotes: 1

Paul92
Paul92

Reputation: 9062

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.

Upvotes: 0

Related Questions