D-Net

SELECT 	p.id 					        as personid,

		p.firstname               		as firstname,		

		p.secondnames                  	as secondnames,

		CASE WHEN length(p.secondnames) > 0 THEN trim(both from p.secondnames||', '||p.firstname) ELSE p.firstname END as fullname,

		p.fax			          		as fax,

		p.email							as email,

		p.phone		               		as phone,

		p.dateofcollection				as dateofcollection,

		p.inferred						as inferred,

		p.deletedbyinference			as deletedbyinference,

		p.trust							as trust,

		p.inferenceprovenance			as inferenceprovenance,

		dc.id							as collectedfromid,

		dc.officialname					as collectedfromname,

		nc.code || '@@@' || nc.name || '@@@' || ns.code || '@@@' || ns.name	as nationality,

		pac.code || '@@@' || pac.name || '@@@' || pas.code || '@@@' || pas.name	as provenanceaction,

		array_agg(DISTINCT i.pid || '###' || idc.code || '@@@' || idc.name || '@@@' || ids.code || '@@@' || ids.name)	as pid

FROM persons p

	left outer join class nc on (nc.code = p.nationalityclass)	

	left outer join scheme ns on (ns.code = p.nationalityscheme)

	left outer join class pac on (pac.code = p.provenanceactionclass)	

	left outer join scheme pas on (pas.code = p.provenanceactionscheme)	

	left outer join datasources dc on (dc.id = p.collectedfrom)

	left outer join personpids pp on (pp.person = p.id)

	left outer join identities i on (i.pid = pp.pid)

	left outer join class idc on (idc.code = i.issuertypeclass)

	left outer join scheme ids on (ids.code = i.issuertypescheme)

	left outer join project_organization po on (po.contactperson = p.id)

WHERE (p.firstname is not null OR p.secondnames is not null) AND po.resporganization is not null

GROUP BY

		p.id,

		p.firstname,		

		p.secondnames,

		p.fax,

		p.email,

		p.phone,

		p.dateofcollection,

		p.inferred,

		p.deletedbyinference,

		p.trust,

		p.inferenceprovenance,

		dc.id,

		dc.officialname,		

		nc.code, nc.name, ns.code, ns.name,

		pac.code, pac.name, pas.code, pas.name