Unable to fetch data from database

Question

I am using the below query to get all the custom field values of my Story Jira, but I see that string value column of custom field value table has numeric values like 1276, 10400, 10423, etc for "Sprint", "K Element", "RoadMap Category" fields.

Need to lookup these values to get the corresponding text values I see on GUI

Query is as below

select cf.cfname,cfv.*
from dbo.customfield cf, dbo.customfieldvalue cfv, dbo.jiraissue task, dbo.project prj
where cf.id = cfv.customfield
and cfv.issue = task.id
and task.issuenum = '1153'
and task.project = prj.id
and prj.pkey = 'HIN';

Tuncay Senturk _Snapbytes_ · Answer

If you join well, you should not get all values.

For instance you should

cfv.stringvalue = cfo.customvalue and csv.customfield = cfo.customfield

But, as Nic said this is not a good way to get the value. You should use API (Jira API or REST API) to get those values.

Prayuja Patil_0z57cw · Answer

I'm getting all the options of the custom field. I want particular value which is present on a ticket.

Nic Brough -Adaptavist- · Answer

For a lot of the fields, you are going to have to join one or many other tables in order to get to what the user sees on-screen, and in some cases, do some calculation as you're going to find it's not in the database.

SQL is the single worst possible way to do any form of reporting on Jira.

Could you explain why you are trying to do this, so we can explain a better way to do it?