CODEWITHSUNDEEP
mysql
Daniel
Daniel

Reputation: 289

Combine two tables and fill in missing values in mysql

I have the following two tables in MySQL:

Table 1:

ID Val1
1  100
2  200
3  300

Table 2:

ID Val2
2  50

How can I combine them into a table like this?

ID Val1 Val2
1  100  0
2  200  50
3  300  0

Upvotes: 2

Views: 1297

Answers (3)

v42
v42

Reputation: 1425

SELECT Table1.ID, Table1.Val1, COALESCE(Table2.Val2, 0) AS Val2
FROM Table1 LEFT OUTER JOIN Table2 USING (ID)
INTO NewTable

Upvotes: 0

Michael Berkowski
Michael Berkowski

Reputation: 270657

Using a LEFT JOIN in conjunction with IFNULL() to fill in zeros for Val2

SELECT 
  Table1.ID,
  Table1.Val1,
  IFNULL(Table2.Val2, 0) AS Val2
FROM Table1 LEFT JOIN Table2 ON Table1.ID = Table2.ID

Upvotes: 0

Bill Karwin
Bill Karwin

Reputation: 562558

SELECT Table1.ID, Table1.Val1, COALESCE(Table2.Val2, 0) AS Val2
FROM Table1 LEFT OUTER JOIN Table2 USING (ID)

Upvotes: 4

Related Questions