mysql query in jira

[srrekanth]

Hi ,

i there any sql query which will give custom field and associated to the projects.Like this i need for all projects what custom fileds are associated to it.

[Thomas Heyne]

yes, there is ...

select * from configurationcontext;
+-------+-----------------+---------+-------------------+-------------------+
| ID    | PROJECTCATEGORY | PROJECT | customfield       | FIELDCONFIGSCHEME |
+-------+-----------------+---------+-------------------+-------------------+
| 10000 |            NULL |    NULL | customfield_10010 |             10000 |
| 10001 |            NULL |    NULL | customfield_10006 |             10001 |
| 10015 |           10001 |   10060 | customfield_10041 |             10013 |
| 10017 |           10001 |   10060 | customfield_10006 |             10014 |

have fun

[Udo Brand]

and if you don't like IDs:

select project.pname Projectname, 
  customfield.cfname, 
  customfield.id  
from customfield, configurationcontext, project
where concat('customfield_', customfield.id) =configurationcontext.customfield
and configurationcontext.project = project.id;

[srrekanth]

Hi Udo,

If i run the query by using * i am not getting all customfields used by a particular project.By the query which you have shared i can get only some fields since we want to clean up some custom fields which are not used i.e from in active projects (i.e last 6 months)

[Udo Brand]

So if I get it correct those customfields are not related to a project. Then I gues in table configurationcontext project is null.

then this should give you the desired result:

select  
  customfield.cfname, 
  customfield.id  
from customfield, configurationcontext
where concat('customfield_', customfield.id) =configurationcontext.customfield
and configurationcontext.project is null;

[Thomas Heyne]

isn't it

select distinct project.pname Projectname, customfield.cfname, customfield.id 
from customfield, configurationcontext, project 
where configurationcontext.project = project.id;

?

[Udo Brand]

hey Thomas, I think Kanthu is looking for customfields not associated with projects (meaning configurationcontext.project is null) so no join with project needed.

[Udo Brand]

Yes, but its up to Kanthu if that is what he wants

[Thomas Heyne]

hi Udo, such results like my first 2 lines in my initial answer?

+-------+-----------------+---------+-------------------+-------------------+
| ID    | PROJECTCATEGORY | PROJECT | customfield       | FIELDCONFIGSCHEME |
+-------+-----------------+---------+-------------------+-------------------+
| 10000 |            NULL |    NULL | customfield_10010 |             10000 |
| 10001 |            NULL |    NULL | customfield_10006 |             10001 |

if so, you are right