Postgres Select Query: SELECT number of appearance in two other tables

Question

I want to select the total number of appearance of a single player in two other tables.

Here is my database structure (postgres):

Table: Player
id              integer

Table: World Champions
id              integer
year            date
player_id       integer

Table: European Champions
id              integer
year            date
player_id       integer

The id on table player is also available in table "World Champions" and "European Champions" (player_id). I want to select the data as following:

player.id       worldChampionTitles     europeanChampionTitles
1               3                       4
2               1                       0
3               0                       0
4               1                       1

But I have no Idea how to write my select query for that.

Left join your tables on player_id and use player.id as grouping field. — Ilya Dyoshin
– Ilya Dyoshin, Commented Feb 10, 2016 at 8:35

user1804599 · Accepted Answer · 2016-02-10 08:36:19Z

1

Easy with subqueries:

SELECT p.id
     , (SELECT count(*) FROM "World Champions" AS c WHERE c.player_id = p.id)
         + (SELECT count(*) FROM "European Champions" AS c WHERE c.player_id = p.id)
FROM Player AS p

answered Feb 10, 2016 at 8:36

user1804599

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgres Select Query: SELECT number of appearance in two other tables

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related