Query SQL - CASE with LEFT JOIN

Asked

Viewed 625 times

2

I need to create a query that returns all active students and their grades for each subject. If the student has no grade for a particular subject should be returned the student, the subject and in note the value "No grade".

I think I’ll need to create using CASE, but I’m not sure yet. Well, I’m not getting to create this query. Can you give me a help?

Below follows the seat structure (reduced)

CREATE TABLE [dbo].[Aluno](
    [codigoAluno] [int] NOT NULL,
    [nome] [varchar](60) NOT NULL,
    [cpf] [decimal](15, 0) NOT NULL,
    [situacao] [bit] NOT NULL,
)
CREATE TABLE [dbo].[Materia](
    [codigoMateria] [int] NOT NULL,
    [nome] [varchar](60) NOT NULL,
    [situacao] [bit] NOT NULL
)
CREATE TABLE [dbo].[Nota](
    [codigoAluno] [int] NOT NULL,
    [codigoMateria] [int] NOT NULL,
    [sequencialMateria] [int] NOT NULL,
    [valor] [decimal](15, 2) NOT NULL,
)

Follow the query I’ve been able to mount so far.

SELECT 
    A.NOME,
    M.NOME, 
    CASE WHEN SUM(M.valor) > 0
             THEN SUM(M.valor)
             ELSE '0' -- Aqui ainda tem que mudar para 'Sem nota'
        END AS 'NOTA'
FROM MATERIA M
LEFT JOIN NOTA N ON M.codigoMateria = N.codigoMateria
LEFT JOIN ALUNO A ON A.codigoALUNO = N.codigoALUNO
--WHERE F.situacao = 1
GROUP BY A.NOME, M.nome, N.valor
ORDER BY A.NOME, M.NOME

In this query, it is only necessary to add the subject whose student has not launched grade.

sql sql-server tsql

  • https://technet.microsoft.com/pt-br/library/ms187518(v=sql.105). aspx search for Outer Join

  • Try changing the line CASE WHEN SUM(M.valor) > 0 for CASE WHEN SUM(M.valor) IS NOT NULL, see if it solves.

  • Pedromvm does not work because it does not display the subject whose student has not launched grade.

  • @Brunoheringer: Should every student do all the subjects in the MATERIA table? // What is the sequential column? // Is it to list each subject as a column? What does the column mean situacao, table MATERIA?

2 answers

2


In this case the ideal would be to use a CROSS JOIN with subquery:

SELECT alu.nome AS aluno,
       mat.nome AS materia,
       ISNULL((SELECT CAST(AVG(not.valor) AS VARCHAR)
                 FROM nota not
                WHERE not.codigoMateria = mat.codigoMateria
                  AND not.codigoAluno = alu.codigoAluno), 'SEM NOTA') AS media 
  FROM materia mat
       CROSS JOIN aluno alu
ORDER BY 1, 2

Explaining the query:

  • The CROSS JOIN combines all table records materia with the table records aluno after all we need a line for each student in each subject;
  • To subquery on the table nota average result (AVG) of the grades for that subject and that student in question;
  • The ISNULL allows the information SEM NOTA appear if no record in the table nota is found (resulting in NULL);
  • We used the CAST(... AS VARCHAR) so that no error occurs when adding the note information with a possible record that returns SEM NOTA;

2

Here is a suggestion to evaluate:

-- código #1 v2
with cteAcumNotas as (
SELECT A.codigoAluno, A.nome as nomeAluno,
       M.codigoMateria, M.nome as nomeMateria,
       sum(N.valor) as valor
  from ALUNO as A
       cross join MATERIA as M
       left join NOTA as N on N.codigoAluno = A.codigoAluno 
                              and N.codigoMateria = M.codigoMateria
  where A.situacao = 1
  group by A.codigoAluno, A.nome, M.codigoMateria, M.nome
)
SELECT codigoAluno, nomeAluno, nomeMateria,
       coalesce(cast(valor as varchar(20)), 'sem nota') as somaNotas
  from cteAcumNotas
  order by nomeAluno, codigoAluno, nomeMateria;

The columns codeAluno and codeMateria were used because of possible homonyms.

Browser other questions tagged sql sql-server tsql

You are not signed in. Login or sign up in order to post.