Doubt - Query Sqlite

Question

I’m having questions on how to write a query. Follow the problem information:

• I have 3 tables, the first being an athlete registration table (id, name, age, country...), a second table with performance data of athletes in summer games (athlete’s id, year of competition, medal (gold, silver or bronze) and a third table with the same data as the previous one but for winter games.

• An athlete may have competed in more than a year and in both summer and winter games. Ex: Athlete A participated in the 2012, 2013 and 2014 summer and 2013 and 2015 winter games. Not necessarily he won medal in all the editions he participated in.

I need to assemble a query that returns to me all athletes who have won more than two gold medals, regardless of whether they went to summer games, winter or both.

Following are examples of table structures.

Tabela A - Cadastro
Id | Nome | Idade | País | Peso | Altura

Tabela B - Jogos de verão
Id_Atleta | Ano | Qntd Medalhas Ouro | Qntd Medalhas Prata | Qntd Medalhas Bronze

Tabela C - Jogos de inverno
Id_Atleta | Ano | Qntd Medalhas Ouro | Qntd Medalhas Prata | Qntd Medalhas Bronze

I need the resulting table to be similar to this:

Id | Nome | Qntd Total Medalhas Ouro (mais do que 2 medalhas)

I tried to write a query using Join and Subqueries, but I couldn’t get to one that brings me the right results. Would anyone have an idea how to write? Please.

Answer 1

Look how good it is to model, if it were I would have built the table B and C in a single placing flag for V - Summertime and I - Winter.

The way it is complicates how easy it is to show the two information of the athlete and his amount of gold medal (being those who have more than two medals), basically in his model is:

SELECT Tabela_A.id, Tabela_A.nome, sum(Tabela_D.quantidade_ouro) as ouro
  FROM Tabela_A 
   LEFT JOIN (
     SELECT * FROM Tabela_B
     UNION ALL
     SELECT * FROM Tabela_C) Tabela_D 
ON Tabela_d.id_atleta = Tabela_A.id
GROUP BY Tabela_A.id, Tabela_A.nome
HAVING ouro > 2

Online Example - http://sqlfiddle.com