mysql query in jira

srrekanth February 6, 2013

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.

1 answer

0 votes
Thomas Heyne
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
February 6, 2013

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
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
February 6, 2013

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 February 6, 2013

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
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
February 6, 2013

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
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
February 6, 2013

isn't it

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

?

Udo Brand
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
February 6, 2013

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
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
February 6, 2013

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

Thomas Heyne
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
February 6, 2013

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

Suggest an answer

Log in or Sign up to answer