postgresql - SQL : How can i join different tables from competition of football?

Question

The problem

I have to make a SQL (postgres) query, to show the names of players, whose teams participate in the "Primeira Liga" and the "Ta?a de Portugal". Also, include the names of players of "Académica" club and all of teams founded in the 1940s. Image of Database design

I'm really stuck and can't figure out, how to connect all tables. Does anyone know how to solve the problem?

Thanks in advance.

Updated (10/01/2021): ** I had to change the names to match my DB, so everyone can understand.

question from:https://stackoverflow.com/questions/65649237/sql-how-can-i-join-different-tables-from-competition-of-football

Answer 1

(SELECT DISTINCT j.nome 
FROM jogador as j 
INNER JOIN equipa as E 
ON E.id = j.equipa_id 
INNER JOIN competicao_equipa as eq 
ON E.id = eq.equipa_id 
WHERE (eq.competicao_id = 1 OR eq.competicao_id = 3))

UNION 

(SELECT j.nome
FROM jogador as j
where equipa_id = 1)

UNION

(SELECT j.nome
FROM jogador as j,equipa
where equipa.fundacao BETWEEN '01/01/1940' AND '31/12/1949')