CODEWITHSUNDEEP
sqlsqlite
F.Johansson
F.Johansson

Reputation: 21

SQL: Combining columns from two tables

I have two tables A and B, each consisting of the columns nr and name. The numbers-column in A is a subset of the numbers-column in B. Now, I would like to create a new table C consisting of the columns nr, name_A and name_B, where name_A is taken from the name-column in A (if the nr exists in A, '-' otherwise) and where name_B is taken from the name-column in B.

The best I have been able to figure out so far is:

CREATE TABLE C
  SELECT * FROM A
    UNION
  SELECT * FROM B;

This is obviously not giving me the structure I ask for, so help would be highly appreciated from a SQL-newbie.

Upvotes: 2

Views: 51

Answers (1)

Mureinik
Mureinik

Reputation: 311143

The tool you're looking for is a [left] join:

CREATE TABLE c AS
SELECT       b.nr, COALESCE(a.name, '-') AS name_a, b.name AS b.name
FROM         b
LEFT JOIN    a on b.nr = a.nr

Upvotes: 2

Related Questions