Paparis Paparis - 5 months ago 19
SQL Question

SQL INSERT INTO from multiple tables

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

You only need one INSERT:

INSERT INTO table3 ( 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