I'm going to do my best to describe this. So I have 3 tables as follows:
|101||Number of exhibits|
|102||Number of stakeholders|
What I'm trying to do is write a query that will produce the below result.
|project_title||Number of exhibits||Number of stakeholders|
I'm not going to paste any of my attempts because they're pathetic.
A couple of notes:
- PostgreSQL version is 9.5 on this db.
- (IMPORTANT) This query will be implemented inside a long query that displays some 65 columns of information about a Project, but for brevity obviously I've omitted some project attributes. I'll post a partial snippet of the current query.
SELECT DISTINCT p.title AS "Project Title", d.name AS "Center/Department", // This is a query that currently exists in the long query I mentioned. (SELECT DISTINCT string_agg(DISTINCT re.name, ', ') AS "Research Exchange" FROM research_exchange re JOIN project_research_exchange pre ON pre.research_exchange_id = re.id WHERE pre.project_id = p.id) FROM project p LEFT JOIN department d ON p.department_id = d.id GROUP BY p.title ORDER BY p.title