Duplicate records in query using SQL Server

Question

Duplicate records in query using SQL Server

Navigation

#1 by (1 votes)
#2 by (0 votes)
#3 by (0 votes)

1

I want the month to show the field EstadoMateria of the table AlumnoMateria without duplicate records and the ones that are not showing anything, as is done in the expected result photo.

This is the result query obtained (which works well)

select IdMateria,NombreMateria
from Materias m
where not exists (select null
                 from AlumnoMaterias am
                where am.idMateria = m.idMateria
                  and am.EstadoMateria in ('aprobado', 'regularizado'))

This query was made by me trying to solve my problem, it does not work because it brings duplicate and nonexistent records.

select Materias.IdMateria,NombreMateria,AlumnoMaterias.EstadoMateria
      from Materias,AlumnoMaterias
      where Materias.IdMateria not in
        (SELECT IdMateria
        FROM AlumnoMaterias
        WHERE (EstadoMateria='Aprobado' or EstadoMateria='Regularizado'))

sql sql-server

asked by Arnaldo 06.01.2017 в 22:59

source

3 answers

Restore a mysql database in java Initialization of values in struct - C ++

score 1 · Answer 1

Personally and for performance reasons I prefer to build queries by JOIN , for which, following your expected result I would use the following query :

SELECT M.IdMateria, M.NombreMateria, AM.EstadoMateria
FROM Materias M
    LEFT JOIN AlumnoMaterias AM 
        ON M.IdMateria = AM.IdMateria

Update

You can review the results here

score 0 · Answer 2

Try this:

select distinct m.IdMateria,NombreMateria,am.EstadoMateria
from Materias m,AlumnoMaterias am
where not exists (select null
             from AlumnoMaterias am
             where am.idMateria = m.idMateria
             and am.EstadoMateria in ('aprobado', 'regularizado'));

practically what changed was adding the word distinct in the query, which will avoid duplicate records.

score 0 · Answer 3

Esta era mi solucion. El left join y le agregue una subconsulta.

It brings me all subjects but excludes the subjects that are in the tbl alumnomaterias with approved or regularized status and shows the status of the subjects that are registered or disapproved, since those that are approved or disapproved in this case I did not want to see them. Thanks to everyone, they helped me a lot.

SELECT M.IdMateria, M.NombreMateria, AM.EstadoMateria,NroAlumno
FROM Materias M
LEFT JOIN AlumnoMaterias AM 
    ON M.IdMateria = AM.IdMateria AND NroAlumno=2016009
    where  (M.IdMateria not in (SELECT IdMateria
                        FROM AlumnoMaterias
                        WHERE  EstadoMateria='Aprobado' or EstadoMateria='regularizado'))