con left join

WITH tabla_estudents AS ( with aw AS (SELECT (t.unw->>'id_award')::INTEGER AS id_award, (t.unw->>'fecha_award')::DATE::TEXT AS fecha_award, (t._id_estudiante)::INTEGER AS _id_estudiante FROM (SELECT _id_estudiante,JSONB_ARRAY_ELEMENTS(json_awards) AS unw FROM notas.notas_estudiante WHERE _id_main=7365) AS t) SELECT --aw.id_award AS id_award, (CASE WHEN aw.id_award IS NOT NULL THEN aw.od_award ELSE null), (CASE WHEN aw._id_estudiante IS NOT NULL THEN aw._id_estudiante ELSE null), (CASE WHEN aw.fecha_award::DATE AS fecha_award IS NOT NULL THEN aw.fecha_award::DATE AS fecha_award ELSE null), (CASE WHEN na.award_points::DATE AS fecha_award IS NOT NULL THEN na.award_points::DATE AS fecha_award ELSE null), --aw._id_estudiante AS _id_estudiante_main, --aw.fecha_award::DATE AS fecha_award, --na.award_points AS award_points, nc.* FROM aw, notas.award na -- notas.__calificaciones_get_estudiante_main__11(7365) nc LEFT JOIN notas.__calificaciones_get_estudiante_main__11(7365) nc ON (aw._id_estudiante = nc._id_estudiante AND aw.id_award = na.id_award) -- WHERE aw._id_estudiante = nc._id_estudiante -- AND aw.id_award = na.id_award ) --select * from tabla_estudents group by _id_estudiante SELECT _id_estudiante, ARRAY_TO_JSON(ARRAY_AGG(JSONB_BUILD_OBJECT('id_award',id_award, 'fecha_award',fecha_award, 'award_points',award_points ) ) ) AS awards FROM tabla_estudents GROUP BY _id_estudiante

Be the first to comment

You can use [html][/html], [css][/css], [php][/php] and more to embed the code. Urls are automatically hyperlinked. Line breaks and paragraphs are automatically generated.