SQL INSERT INTO from multiple tables

Question

this is my table 1:

NAME       AGE        SEX        CITY             ID  Clara      22         f          New York         1 Bob        33         m          Washington       2 Sam        25         m          Boston           3 

this is my table 2:

NUMBER       ID 555-1111     1 555-2222     2 555-3333     3 

and now I want a table 3 which shows me all information:

NAME       AGE        SEX        CITY             ID        NUMBER  Clara      22         f          New York         1         555-1111 Bob        33         m          Washington       2         555-2222 Sam        25         m          Boston           3         555-3333 

I tried first to insert into table 3 only the values from table 1 and then I inserted into table 3 the values from table 2 with an inner join where Id = Id is.

INSERT INTO table3 { name, age, sex, city, id} SELECT name, age, sex, city, id FROM table 1    INSERT INTO table3 { name, age, sex, city, id, number} SELECT name, age, sex, city, id, number FROM table 2 p INNER JOIN table 3 c ON c.Id = p.Id 

But all I get is a duplication of my values. instead of having 3 entries, I have like 9 entries, which some have number null, some have only the number and the rest null, and some are correct.

I hope someone can help me

EDIT

If I am having now a third Table like this one:

NATIONALITY       ID  Canadian          1 American          2 French            3 

How could I merge all 3 tables into one Table?

Answer 1

You only need one INSERT:

INSERT INTO table4 ( name, age, sex, city, id, number, nationality) SELECT name, age, sex, city, p.id, number, n.nationality FROM table1 p INNER JOIN table2 c ON c.Id = p.Id INNER JOIN table3 n ON p.Id = n.Id