SQL Query - CASE with LEFT JOIN

Question

SQL Query - CASE with LEFT JOIN

Navigation

#1 by (2 votes)
#2 by (2 votes)

2

I need to create a query that returns all active students and their grades for each subject. If the student does not have a grade for a given subject, the student must return the subject and note the value "No grade".

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

Below is the structure of the bank (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,
)

Here is the query that I have been able to put together 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 does not have a note released.

sql sql-server tsql

asked by anonymous 20.05.2017 / 23:38

2 answers

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.

21.05.2017 / 19:40

regex to replace the $ _POST ['field'] string with $ this-input-post ('field') of codeigniter Name parameter in Django's URLconfs

score 2 · Accepted Answer

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 query :

The CROSS JOIN combines all the records of the materia table with the records of the aluno table after all we need a row for each student in each subject;
A subquery in table nota result in average ( AVG ) of grades released for that subject and that student in question;
The ISNULL allows the SEM NOTA information to appear if no record in the nota table is found (resulting in NULL );
We use CAST(... AS VARCHAR) so that an error does not occur when joining the note information with some possible record that returns SEM NOTA ;