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.
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
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;
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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)
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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;
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
isn't it
select distinct project.pname Projectname, customfield.cfname, customfield.id from customfield, configurationcontext, project where configurationcontext.project = project.id;
?
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
hey Thomas, I think Kanthu is looking for customfields not associated with projects (meaning configurationcontext.project is null) so no join with project needed.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Yes, but its up to Kanthu if that is what he wants
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.