Choose maximum data with 3 tables

Question

I can not get the right results. I have 3 tables:

table: Aluno
id_aluno    nome
1           Bruno
2           Carlos

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

I need the following results:

nome     descricao    data
Bruno    TIPO B       2015-12-12
Carlos   TIPO A       2015-12-10       

The problem is that the GROUP BY clause must have an id_aluno column, but this is not a table foreign key that has a date. Between them there is an intermediate table. I do not know how to do that. I hope you help me.

Answer 1

You can join tables based on existing keys, but then you will need to specify that you want the maximum date to be based only on a person, something like this:

SELECT
    a.nome,
    s.descricao,
    t.data
FROM Aluno a
    JOIN Serie s
        ON s.id_aluno = a.id_aluno
    JOIN Treino t
        ON t.id_serie = s.id_serie
WHERE t.data = ( --get max date by person, excluding serie
                SELECT MAX(t1.data)
                FROM Aluno a1
                JOIN Serie s1
                    ON s1.id_aluno = a1.id_aluno
                JOIN Treino t1
                    ON t1.id_serie = s1.id_serie
                WHERE s1.id_aluno = s.id_aluno 
               )

Answer 2

Group By From, , Join. Join, , .

Select Aluno.nome, Serie.descricao, Max(Treino.data) As data
From Aluno
Inner Join Serie On Serie.id_aluno = Aluno.id_aluno
Inner Join Treino On Treino.id_serie = Serie.id_serie
Group By Aluno.nome, Serie.descricao

, MS SQL :

Select nome, descricao, data
From (
    Select Aluno.nome, Serie.descricao, Treino.data,
        RANK() OVER (Partition By Aluno.nome Order By Treino.data Desc) AS Ordinal
    From Aluno
    Inner Join Serie On Serie.id_aluno = Aluno.id_aluno
    Inner Join Treino On Treino.id_serie = Serie.id_serie
) Ranked
Where Ordinal = 1

, , Treino: 4, 1, 2015-12-12 5, 2, 2015-12-12, ? . :

Select nome, descricao, data
From (
    Select Aluno.nome, Serie.descricao, Treino.data,
        ROW_NUMBER() OVER (Partition By Aluno.nome Order By Treino.data Desc, Serie.descricao) AS Ordinal
    From Aluno
    Inner Join Serie On Serie.id_aluno = Aluno.id_aluno
    Inner Join Treino On Treino.id_serie = Serie.id_serie
) Ranked
Where Ordinal = 1

Answer 3

Use this query

Select a.nome, s.descricao, t.data
FROM Aluno a
join Serie s on (s.id_aluno = a.id_aluno)
join Treino t on (t.id_serie = s.id_serie)

Run codeHide result

Answer 4

Here I did this using a few common table expressions.

Script

WITH CTE
AS
(
    SELECT TOP 2 id_Treino,id_serie,data
    FROM Treino
    ORDER BY DATA DESC
), 
cte2 
as
(
    SELECT a.nome,s.descricao,c.data
    FROM aluno AS a
    INNER JOIN Serie AS s
    ON a.id_aluno = s.id_aluno
    INNER JOIN cte AS c
    ON s.id_serie = c.id_serie
)
SELECT * FROM cte2;

Exit

nome        descricao       DATA
------------------------------
Bruno       Tipo B      2015-12-12
Carlos      Tipo A      2015-12-10