[psp] views na poslance

[kokes]

A asi i jiný orgány atd.

SELECT
	os.id_osoba,
	os.jmeno,
	os.prijmeni,
	os.narozeni,
	org.id_organ,
	org.zkratka,
	org.nazev_organu_cz,
	od_o
FROM
	psp.poslanci_osoby os
	INNER JOIN psp.poslanci_zarazeni zr ON zr.id_osoba = os.id_osoba
	INNER JOIN psp.poslanci_organy org ON org.id_organ = zr.id_of
WHERE
	cl_funkce = 0
	AND zr.do_o IS NULL
	AND zkratka = 'PSP9'

[kokes]

SELECT
	os.id_osoba,
	os.jmeno,
	os.prijmeni,
	os.narozeni,
	string_agg(org.nazev_organu_cz, ', ')
FROM
	psp.poslanci_osoby os
	INNER JOIN psp.poslanci_zarazeni zr ON zr.id_osoba = os.id_osoba
	INNER JOIN psp.poslanci_organy org ON org.id_organ = zr.id_of
WHERE
	cl_funkce = 0
	AND zr.do_o IS NULL
	-- AND zkratka = 'PSP9'
GROUP BY 1, 2, 3, 4

[kokes]

Příklad použití

WITH posl AS (
	SELECT
		os.id_osoba,
		os.jmeno,
		os.prijmeni,
		os.narozeni,
		string_agg(org.nazev_organu_cz,
			', ') organy
	FROM
		psp.poslanci_osoby os
		INNER JOIN psp.poslanci_zarazeni zr ON zr.id_osoba = os.id_osoba
		INNER JOIN psp.poslanci_organy org ON org.id_organ = zr.id_of
	WHERE
		cl_funkce = 0
		AND zr.do_o IS NULL
		-- AND zkratka = 'PSP9'
	GROUP BY
		1,
		2,
		3,
		4
)
SELECT
	*
FROM
	justice.skutecny_majitel sm
	INNER JOIN posl ON lower(sm.osoba_jmeno) = lower(jmeno)
		AND lower(sm.osoba_prijmeni) = lower(prijmeni)
		AND sm.osoba_datum_narozeni = narozeni
	-- tohle by chtelo uklidit trochu - on ten subjekt mohl zaniknout v mezicase
	-- my ale chceme maximalne jednu radku z tech nazvu... asi to bude chtit CTE
	LEFT JOIN justice.nazev on nazev.ico = sm.ico and nazev.datum_vymaz IS NULL
LIMIT 1000