Display data without repeating

Question

Display data without repeating

Navigation

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

2

I need help with my sql query. My query is:

select * from matricula

and show me this information:

nummatri   fechamatri    dnialum    idnivel   idgrado  idsecc   año
3          2017-12-20    12357890    2          7       14      2018

I want you to show me the description of each id, that is, instead of

2=secundaria
7=primero
14=A

I use this query:

SELECT    Distinct  m.nummatr,m.fechamat, m.idalumno,
n.descnivel,g.descgrado, s.descsecc
FROM            
matricula as m INNER JOIN
nivel AS N ON m.idnivel = n.idnivel INNER JOIN
grado as g ON n.idnivel = g.idnivel INNER JOIN
seccion as s ON n.idnivel = s.idnivel AND g.idgrado = s.idgrado

but it shows me this data:

3   2017-12-20  12357890    Secundaria  Cuarto  A

3   2017-12-20  12357890    Secundaria  Cuarto  B

3   2017-12-20  12357890    Secundaria  Primero A

3   2017-12-20  12357890    Secundaria  Primero B

3   2017-12-20  12357890    Secundaria  Quinto  A

3   2017-12-20  12357890    Secundaria  Quinto  B

3   2017-12-20  12357890    Secundaria  Segundo A

3   2017-12-20  12357890    Secundaria  Segundo B

3   2017-12-20  12357890    Secundaria  Tercero A

3   2017-12-20  12357890    Secundaria  Tercero B

What would be the correct query to show me what I want?

sql sql-server

asked by Adrian 22.12.2017 в 03:36

source

2 answers

0

You should do something like this:

SELECT m.nummatr, m.fechamat, m.idalumno, n.campoNombreTablaNivel, g.campoNombreTablaGrado, s.campoNombreTablaSeccion FROM matricula m, level n, grade g, section s WHERE m.idnivel = n.idnivel AND m.graduate = g.class AND m.section = s.section

IMPORTANT:

The level tables must have primary ker idlevel, the same for the grade and section tables

example: alter table level add constraint PK_NIVEL primary key (idlevel)

and the enrollment table must refer to the level, grade and section tables, that is, a foreign key

example: alter table enrollment add constraint CONS_001 foreign key (idnivel) references level (idnivel)

and that the idnivel, idgrado and idseccion fields do not allow nulls,

all this to ensure good data integrity

answered by 22.12.2017 в 20:30

Why do I have to put two references to the GoogleMaps APIs? Progress Bar Dynamic Bootstrap MySQL

score 1 · Accepted Answer

The problem is with the joins you make to the tables grado and nivel .

In your query, you are doing joins to those 2 tables from the table nivel . This does not seem right. Actually, the 3 tables should relate directly to the main table matricula .

I may be a little wrong with the column names, because you did not describe all the tables, but the query should be as follows:

SELECT m.nummatr,
       m.fechamat,
       m.idalumno,
       n.descnivel,
       g.descgrado,
       s.descsecc
  FROM matricula AS m
 INNER JOIN nivel AS n ON m.idnivel = n.idnivel
 INNER JOIN grado AS g ON m.idgrado = g.idgrado
 INNER JOIN seccion AS s ON m.idsecc = s.idsecc

Note that if the query is designed correctly, then there is no need to include a DISTINCT , which is the keyword that we instinctively add to the query when it returns duplicates that we do not understand.