Postgres Join 3 Tables(Timestamp)

Question

I have 3 tables and need mutliple values from them. I need pname(project), lead(project), author(changegroup) and updated(jiraissue)/created(changegroup). pname and lead are project name and leader of the project. Author is the one who changed the ticket the last time, and update and created are the timestamps from that action. But update and created are often not the same. So cant use them to filter. I have no problem to get pname, lead, and update. But I need the author too.

SELECT pname, lead, MAX(updated) FROM project join jiraissue on project.id=jiraissue.project GROUP BY(pname, lead);

That is the query command to get pname, lead, and updated. I filter the all tickets from a project, and look which was edited the most recently. I take that timestamp for the time the entire project was edited the last time. But couldn't create an command to get the author too. you help me?

Project Table:

The Project itself with project name and lead| public.id=jiraissue.project

Jira Issue Table:

jiraissues are all tickets from the projects |project.id=jiraissue.project, jiraissue.updated is most times changegroup.created, changegroup.issueid=jiraissue.id

Changegroup Table:

changegroup is a list of the times when a ticket is edited| changegroup.issueid=jiraissue.id, changegroup.created=jiraissue.updated

score 0 · Answer 1 · answered Jul 13 '22 at 05:25

Your explanation left me confused. But here is my educated guess:

SELECT p.pname, p.lead, c.author, j.updated, c.created
FROM   project p
LEFT   JOIN (
   SELECT DISTINCT ON (j.project)
          j.project, j.updated, j.id
   FROM   jiraissue j
   ORDER  BY j.project, j.updated DESC NULLS LAST
   ) j ON j.project = p.id
LEFT   JOIN changegroup c ON c.issueid = j.id;

Another case for DISTINCT ON. See:

Select first row in each GROUP BY group?

Eliminating unneeded rows before joining should be the fastest way.

I use 2x LEFT JOIN retain projects without any Jira issues in the result.

Postgres Join 3 Tables(Timestamp)

1 Answers1