CODEWITHSUNDEEP
sqlpostgresql
Paulo Hgo
Paulo Hgo

Reputation: 860

Merging 2 queries with Postgres SQL

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.

Upvotes: 0

Views: 61

Answers (1)

Jon Armstrong
Jon Armstrong

Reputation: 4694

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

Upvotes: 1

Related Questions