mardi 21 avril 2015

SQL union of two tables with different columns

I want to get one result set of rows back from two tables with different column names, one line per line in each table.

Result should look like this, blanks can be null, team_id in 2nd half comes from coach_id:

-----------------------------------------
player_id | team_id | score | improvement
-----------------------------------------
11          20         5
11          21         4
12          22         2
12          23         2
11          20                   5
11          21                   6
12          21                   5
13          23                   10

Here's the schema:

CREATE TABLE coaches
    (`id` int, `team_id` int)
;

INSERT INTO coaches
    (`id`, `team_id`)
VALUES
    (1, 20),
    (2, 21),
    (3, 22),
    (4, 23)
;

CREATE TABLE players
 (`id` int, `player_id` int);


INSERT INTO players
(`id`, `player_id`)
VALUES
(1,11),
(2,12),
(3,13),
(4,14)
;
CREATE TABLE games
    (`id` int, `player_id` int, `team_id` int, `score` int)
;

INSERT INTO games
    (`id`, `player_id`, `team_id`, `score`)
VALUES
    (1, 11, 20, 5),
    (2, 11, 21, 4),
    (3, 12, 22, 2),
    (4, 12, 23, 2)
;

CREATE TABLE sessions
    (`id` int, `player_id` int, `coach_id` int, `improvement` int)
;

INSERT INTO sessions
      (`id`, `player_id`, `coach_id`, `improvement`)
VALUES
    (1, 11, 1, 5),
    (2, 11, 2, 6),
    (3, 12, 2, 5),
    (4, 13, 4, 10)
;

Tried this, but it wasn't really close:

SELECT tweets.player_id
      ,tweets.team_id
      ,follows.coach_id 
FROM tweets FULL OUTER JOIN follows ON (1 = 0);

Aucun commentaire:

Enregistrer un commentaire