aspek_kk_issue_2.1_check

SELECT
	jawaban.*,
	files.id,
	files.created_at,
	files.disk_name,
	files.file_name,
	files.attachment_id,
	CONCAT(
		"https://aspek.ombudsman.go.id/storage/app/uploads/public/",
		CONCAT(
			SUBSTRING(files.disk_name, 1, 3),
			"/",
			SUBSTRING(files.disk_name, 4, 3)),
		CONCAT(
			"/",
			SUBSTRING(files.disk_name, 7, 3),
			"/", files.disk_name)
	) AS 'Url Path'

FROM
	aspek_kepatuhan_detail_jawaban as jawaban
LEFT JOIN
	system_files as files ON files.attachment_id = jawaban.id
JOIN
	aspek_kepatuhan_profile_responden as responden ON responden.id = jawaban.profil_responden_id
WHERE
	responden.id IN (
		SELECT
			responden.id
		FROM
			aspek_kepatuhan_profile_responden as responden
		WHERE
			institution_id = (
			SELECT
				institution_id
			FROM
				aspek_kepatuhan_profile_responden
			WHERE
				id = 37832
			)
		ORDER BY
			responden.id
	)
AND jawaban.questions_id = 20
ORDER BY
	jawaban.profil_responden_id, jawaban.questions_id