SQL Script for Issuetypes

[HaRsHaS' V]

I am trying to set a relationship between Workflows and Issue types. I did get it to a certain extent. Thanks to Community. However, I am unable to get the unassigned issuetypes in the table result.

Below is the script I have used. It would be great if anyone can advice n how to get unassigned issuetypes too in the picture

select distinct workflow,pname from workflowschemeentity w
join nodeassociation n on n.sink_node_id=w.scheme
join issuetype it on w.issuetype=it.ID

[Nic Brough -Adaptavist-]

You won't be able to "get" unassigned issue types, there is nothing in the database for them.  You'll have to infer it from the absence of data.

This is one of many reasons that using SQL on a Jira database is by far the worst way to get any information out of it.

Instead of messing around with a data structure that has no relations (because they are all done in the code, not the data), could you explain what you are trying to achieve?  We can almost certainly give you a better way to do it than "read it with SQL"

[HaRsHaS' V]

@Nic Brough -Adaptavist- I am trying to get the list of workflows and the associated Workflows in a table using SQL Query

[Nic Brough -Adaptavist-]

That does not tell us what you are trying to achieve, you've just repeated you're trying to do something in a bad way.

[Florian]

Without having access to my Jira instance right now my guess is this might help:

select distinct workflow, pname

from issuetype it

left join workflowschemeentity w 

on w.issuetype = it.id

I don’t know what the table nodeassociation in your script does and right now I cannot test it. Basically a “left join” says take everything from the left table (issuetype) and look for a match in the right table (workflowschemeentity). If you don’t find something in the right right table keep the left value and assume right to be empty (null). 

[HaRsHaS' V]

@Florian

I really appreciate your response. A big thanks. I tried this out. It gives a result to a certain level.the unassigned issue types is showing up(This is good), However, the corresponding workflows for the unassigned  issue types are not being shown up. It shows NULL. is there any way we can capture them too?

[Florian]

Hi @HaRsHaS' V

that is as I described. When a left join does not find anything on the right side the result shows NULL. Maybe you can provide some screenshots of your configuration and a mock-up of the results you expect. The I can try to understand the underlying data structure in the database and post a SQL query. It may take a few days for me to squeeze you in...

[HaRsHaS' V]

@Florian , Thanks I will send the details to you. Thanks, I was occupied with something else and was not able to look into this.