join using composite primary keys

My query consists of 4 tables , two of them have composite PK,

(example):
Table : ced_inscripcion
--------------------------
cein_cecu_codigo(PK) cein_cvcu_version(PK) cein_csvc_seccion(PK) cein_rut(PK)
1 1 1 124539812
1 1 1 134562343
1 2 1 178923333

Table : ced_version_curso
---------------------------------
cecu_codigo(PK) cvcu_version(PK) cvcu_anio_curso
1 1 2018
1 2 2018
1 3 2018
1 4 2019
2 1 2017
2 2 2017


the count for unique registered students in ced_inscripcion table is 5962 , after joining tables to ced_version_curso where I need to get the year the student took a course from column cvcu_anio_curso, the number of rows jump to 8716, being that I only have 5962 unique student registered.

query:

SELECT DISTINCT
ced_inscripcion.cein_rut Rut,
a.alum_antalu_rut,
a.alum_nombres Nombre,
a.alum_paterno Apellido,
sedes.sede_nombre Sede,
SUBSTRING(ced_version_curso.cvcu_anio_curso::varchar, 1, 4) Anio
//strip last digit to leave year only

FROM
ced_inscripcion
LEFT JOIN
(SELECT DISTINCT ON (alum_antalu_rut)* FROM alumnos) a
ON ced_inscripcion.cein_rut = a.alum_antalu_rut
LEFT JOIN
sedes ON
a.alum_cod_sede = sedes.sede_codigo
LEFT JOIN
ced_version_curso ON
(ced_inscripcion.cein_cecu_codigo = ced_version_curso.cvcu_cecu_codigo
AND
ced_inscripcion.cein_cvcu_version = ced_version_curso.cvcu_version)
GROUP BY
ced_inscripcion.cein_rut,
a.alum_antalu_rut,
a.alum_nombres,
a.alum_paterno,
sedes.sede_nombre,
ced_version_curso.cvcu_anio_curso


the attached images display the result before and after join with ced_version_curso table

After joing ced_version_curso table rows count increase.



thanks in advance!

Continue reading...