Merging 2 queries with Postgres SQL

Question

This may look like a basic question but I am having serious trouble finding a solution for it. I'm querying the Jira database directly and that works fine. I do have 2 queries that spit out a date that a ticket has transitioned from certain status to another set of statuses. Then I have a second query that checks the same thing for when it changes to DONE. I'm looking to merge these 2 queries into a single one.

Query 1:

    SELECT distinct on (i.issuenum) issuenum, to_char(cg.created, 'DD-MON-YYYY') as datemoved, ci.oldvalue, ci.newvalue from changeitem ci 
inner join changegroup cg on cg.id = ci.groupid
inner join jiraissue i on i.id = cg.issueid
inner join issuestatus s on s.id = i.issuestatus
inner join project p on p.id = i.project
where ci.oldvalue in ('10400') and ci.newvalue in ('10904', '10504', '3', '10000') and project = 11009 and s.id = '10000'
order by i.issuenum asc

Query 2:

SELECT distinct on (i.issuenum) issuenum, to_char(cg.created, 'DD-MON-YYYY') as datemoved, 
filter (where ci.newvalue = '10000', to_char(cg.created, 'DD-MON-YYYY') 
ci.oldvalue, ci.newvalue  from changeitem ci 
inner join changegroup cg on cg.id = ci.groupid
inner join jiraissue i on i.id = cg.issueid
inner join issuestatus s on s.id = i.issuestatus
inner join project p on p.id = i.project
where ci.oldvalue in ('10400') and ci.newvalue in ('10904', '10504', '3', '10000') and project = 11009 and s.id = '10000'
order by i.issuenum asc

As you can see, both queries are identical except for the conditions. I get the 2 outputs and put them side to side to get the output that I need, which is obviously not the right way of doing it. Appreciate any pointers here.

there is something wrong with your query 2: filter should be used with aggregate or window functions, the opening "(" near "filter" has no closing ")". — Julius Tuskenis
– Julius Tuskenis, Commented Aug 13, 2021 at 7:22

Jon Armstrong · Accepted Answer · 2021-08-13 11:50:38Z

If you want the query results paired, side by side, you want a JOIN. A general way to do that is:

WITH q1 AS ( query1 )
   , q2 AS ( query2 )
SELECT q1.*
     , q2.*
  FROM q1
  JOIN q2
    ON q1.issuenum = q2.issuenum
 ORDER BY q1.issuenum
;

Ask a question about outer joins if you have rows in either query result that have no match in the other, and you wish to return those results as well.

and with your SQL:

WITH q1 AS (
      SELECT distinct on (i.issuenum) issuenum, to_char(cg.created, 'DD-MON-YYYY') as datemoved, ci.oldvalue, ci.newvalue from changeitem ci 
        join changegroup cg on cg.id = ci.groupid
        join jiraissue i on i.id = cg.issueid
        join issuestatus s on s.id = i.issuestatus
        join project p on p.id = i.project
       where ci.oldvalue in ('10400') and ci.newvalue in ('10904', '10504', '3', '10000') and project = 11009 and s.id = '10000'
       order by i.issuenum asc
     )
   , q2 AS (
      -- This is just a copy of your first query, as an example of the form
      SELECT distinct on (i.issuenum) issuenum, to_char(cg.created, 'DD-MON-YYYY') as datemoved, ci.oldvalue, ci.newvalue from changeitem ci 
        join changegroup cg on cg.id = ci.groupid
        join jiraissue i on i.id = cg.issueid
        join issuestatus s on s.id = i.issuestatus
        join project p on p.id = i.project
       where ci.oldvalue in ('10400') and ci.newvalue in ('10904', '10504', '3', '10000') and project = 11009 and s.id = '10000'
       order by i.issuenum asc
     )
SELECT q1.*
     , q2.*
  FROM q1
  JOIN q2
    ON q1.issuenum = q2.issuenum
 ORDER BY q1.issuenum
;

You can use the following fiddle to work on the errors in Query 2:

Full working example

Collectives™ on Stack Overflow

Merging 2 queries with Postgres SQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related