Extracting Data from Jira

[Om Joshi]

Hi I have the below query and i would like to add details from project table.

SELECT Jiraissue.project "Project ID", JIRAISSUE.id "Issue ID", JIRAISSUE.summary, ENTITY_PROPERTY.*

FROM ENTITY_PROPERTY

INNER JOIN JIRAISSUE ON ENTITY_PROPERTY.entity_id =JIRAISSUE.id

WHERE ENTITY_PROPERTY.property_key = 'com.greenyloop.definitionofready' or

ENTITY_PROPERTY.property_key =  'com.greenyloop.acceptancecriteria' or

ENTITY_PROPERTY.property_key =  'com.greenyloop.dod'

;

the details I would like to include from Project table is Project name, Project Key, Project Lead/owner.

is there any way to link I couldn't use another INNER JOIN and include project as not sure how to.

[Florian Bonniec · Accepted solution]

Hi

The query below should return what you ar eexpecting.

SELECT Jiraissue.project "Project ID", project.pname, project.pkey, project.lead ,JIRAISSUE.id "Issue ID", JIRAISSUE.summary, ENTITY_PROPERTY.*
FROM ENTITY_PROPERTY
INNER JOIN project on Jiraissue.project = project.id
INNER JOIN JIRAISSUE ON ENTITY_PROPERTY.entity_id =JIRAISSUE.id

WHERE ENTITY_PROPERTY.property_key = 'com.greenyloop.definitionofready' or

ENTITY_PROPERTY.property_key = 'com.greenyloop.acceptancecriteria' or

ENTITY_PROPERTY.property_key = 'com.greenyloop.dod'

;

Regards

[Om Joshi]

Hi thanks Florian

I have already tried that and getting this error...

ERROR: missing FROM-clause entry for table "jiraissue"

LINE 3: INNER JOIN project on JIRAISSUE.project = project.id

[Florian Bonniec]

Can you try 

SELECT Jiraissue.project "Project ID", project.pname, project.pkey, project.lead ,JIRAISSUE.id "Issue ID", JIRAISSUE.summary, ENTITY_PROPERTY.*
FROM ENTITY_PROPERTY
INNER JOIN JIRAISSUE ON ENTITY_PROPERTY.entity_id =JIRAISSUE.id
INNER JOIN project on Jiraissue.project = project.id

WHERE ENTITY_PROPERTY.property_key = 'com.greenyloop.definitionofready' or

ENTITY_PROPERTY.property_key = 'com.greenyloop.acceptancecriteria' or

ENTITY_PROPERTY.property_key = 'com.greenyloop.dod'

Regards

[Om Joshi]

thanks you so much! 

I wish I had tried to swap the inner joins..

regards,

OJ