CODEWITHSUNDEEP
mysqlsqldatemax
Thiago Braga
Thiago Braga

Reputation: 129

MYSQL LEFT JOIN ON RELATED TABLE

I have 4 tables and a SQL which is returning almost what I want. It envolves max dates with related tables and a intermediate table. Last question was answered, but I realized I needed more in sql, which is returning null if there's no record in related tables for the id_aluno foreign key.

table: Aluno
id_aluno    nome
1           Bruno
2           Carlos
3           Fernando

table: Serie
id_serie    id_aluno    descricao
1           1           Tipo A
2           1           Tipo B
3           2           Tipo A

table: Treino
id_treino   id_serie    data
1           1           2015-12-10
2           2           2015-12-12
3           3           2015-12-10

table: Avaliacao
id_avaliacao   id_aluno   data_avaliacao
1              1          2015-12-07
2              1          2015-12-01
3              2          2015-12-05
4              2          2015-12-04

I want the following results:

nome        descricao    data          data_avaliacao
Bruno       TIPO B       2015-12-12    2015-12-07
Carlos      TIPO A       2015-12-10    2015-12-05
Fernando    null         null          null

But the following SQL doesn't returns me a "aluno" (a.nome) if there is no data on related tables. I tried LEFT JOIN, but it's not the way I want.

SELECT a.nome, s.descricao, t.data, v.data_avaliacao
  FROM aluno a
  JOIN serie s
    ON s.id_aluno = a.id_aluno
  JOIN treino t
    ON t.id_serie = s.id_serie
  JOIN 
     ( SELECT s.id_aluno
            , MAX(t.data) max_data 
         FROM serie s 
         JOIN treino t 
           ON t.id_serie = s.id_serie 
        GROUP 
           BY id_aluno
     ) x
    ON x.id_aluno = s.id_aluno
   AND x.max_data = t.data
JOIN avaliacao v
        ON s.id_aluno = v.id_aluno
WHERE v.data_avaliacao IN
   ( SELECT MAX(v.data_avaliacao) max_data1 
                FROM avaliacao v
        GROUP 
           BY id_aluno
     )

I hope you can help. Thank you

Upvotes: 0

Views: 57

Answers (2)

Thiago Braga
Thiago Braga

Reputation: 129

I got it!

SELECT nome, T.descricao, T.data, data_avaliacao  
FROM   aluno A 
LEFT OUTER JOIN (SELECT s.id_aluno, s.descricao, max(t.data) data 
            FROM serie s 
            JOIN treino t ON t.id_serie = s.id_serie 
            GROUP BY id_aluno) T ON T.id_aluno = A.id_aluno AND A.id_aluno = T.id_aluno 
LEFT OUTER JOIN (SELECT max(v.data_avaliacao) data_avaliacao, id_aluno 
            FROM   avaliacao_aluno v 
            GROUP  BY id_aluno) V ON v.id_aluno = A.id_aluno

Upvotes: 0

Pரதீப்
Pரதீப்

Reputation: 93724

Try something like this

SELECT nome, 
       descricao, 
       data, 
       data_avaliacao 
FROM   aluno A 
       LEFT OUTER JOIN serie S 
                    ON A.id_aluno = S.id_aluno 
       LEFT OUTER JOIN (SELECT s.id_aluno, 
                               Max(t.data) data 
                        FROM   serie s 
                               JOIN treino t 
                                 ON t.id_serie = s.id_serie 
                        GROUP  BY id_aluno) T 
                    ON T.id_aluno = A.id_aluno 
       LEFT OUTER JOIN (SELECT Max(v.data_avaliacao) data_avaliacao,
                               id_aluno 
                        FROM   avaliacao v 
                        GROUP  BY id_aluno) V
                    ON v.id_aluno = A.id_aluno

Upvotes: 1

Related Questions