Page Comparison

...

This is a very interesting use case, as it many cases as  you can write your query without using this table in a lot of cases. Pay attention.

DEMO-1 blocks →  DEMO-2

...

SELECT
 KEY(JQl.ISSUE) as "Source",
 CASEWHEN(IL.DIRECTION = 'outward', ILT.OUTWARDDESCRIPTION, ILT.INWARDDESCRIPTION) AS "Link",
 KEY(JQL2.ISSUE) as "Target"
FROM
 JQL
  JOIN
 ISSUELINKS IL ON IL.ISSUEID = JQL.ISSUEID
  JOIN
 ISSUELINKDEFINITIONS ILT ON ILT.ID = IL.ISSUELINKTYPEID
  JOIN
 JQL JQL2 ON JQL2.ISSUEID = IL.CHILDISSUEID
WHERE 
 JQL.QUERY = ' issue = DEMO-1 '
   AND
 RESOLVED(JQL2.ISSUE) IS NOT NULL

The query above

...

SELECT
  KEY(JQL.ISSUEIDISSUE) as "IssueSource",
  'issue in linkedissues("' || JQL.ISSUEID || '", "blocks") AND RESOLUTION IS EMPTY' as "Target JQL"
FROM 
  JQL 
WHERE WHERE JQL.QUERY = ' issue = DEMO-1 '

Please pay attention to the single and double quotes enclosing stings:

• Single quotes are for SQL strings
• Double quotes are for JQL strings

Now, you got a JQL that has to be performed for each issue. So, use the JQL table to run them:

SELECT
 "Source" , 'blocks' as "Link" , KEY(JQL.ISSUE) as "Target"
FROM 
(
SELECT
 KEY(JQL.ISSUE) as "Source",
 'issue in linkedissues("' || JQL.ISSUEID || '", "blocks") AND RESOLUTION IS EMPTY' as "Target JQL"
FROM 
 JQL 
WHERE JQL.QUERY = ' issue = DEMO-1 '
) X LEFT JOIN JQL ON JQL.QUERY = X."Target JQL"

You got just the same results. This is a good example of the flexibility of SQL+JQL.

In the query above note:

• A SELECT is used as part of a join, just like it was a table. Well, SELECT produces tables in SQL. And this table has the "X" alias.
• LEFT JOIN is used to ensure that the "X" table is performed first
• The "X" table has two columns: "Source" and "Target JQL" than can be referenced outside the encloser brackets.

How does the query above works?

1. X is performed and the issue key and a JQL query string is build for each issue.
2. The JQL built in (1) is performed via join JQL to get the unresolved blocked issues.

Interesting, no?