CODEWITHSUNDEEP
sqlpostgresql
mtbomb
mtbomb

Reputation: 1117

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);

Upvotes: 7

Views: 25984

Answers (2)

SimarjeetSingh Panghlia
SimarjeetSingh Panghlia

Reputation: 2200

nulnullry this

 SELECT player_id
    ,team_id
    ,score
    ,NULL AS improvement
FROM games
UNION All
SELECT sessions.player_id
    ,coaches.team_id
    ,NULL AS score
    ,sessions.improvement
FROM sessions
INNER JOIN coaches ON coaches.id = sessions.coach_id

Upvotes: 4

Lennart - Slava Ukraini
Lennart - Slava Ukraini

Reputation: 7171

Something like:

select player_id
     , team_id
     , score
     , cast(null as int) as improvement 
from games 
union all 
select s.player_id
     , c.team_id
     , cast(null as int) as score
     , s.improvement 
from sessions as s 
join coaches as c 
    on s.coach_id = c.id 
order by score

should work

Upvotes: 1

Related Questions