How to get selected option value in customfield

[Anh Nguyen]

Hi

I am having a difficulty in getting selected option value in customfield jira. 

Here is my query 

select cfv.issue, cfv.*, cfo.*, cf.cfname , issue.pkey
from CustomFieldValue cfv
join CustomField CF on CF.Id = CFV.CustomField
join CustomFieldOption CFO on CF.Id = CFO.CustomField
join jiraissue issue on issue.id = cfv.issue
where (cf.CFName = 'SRS')
AND issue.issuenum = 10565

issue number 10565 has custom field SRS and value = YES 

But when I run the query I still see both YES/NO option and I DO NOT know which significant show YES selected as in JIRA screen

Here is the result (there are 2 rows with Yes, No)

86579 828558 86579 10600 NULL 10500 NULL NULL NULL NULL 10500 10600 10700 NULL 0 Yes NULL N SRS NULL
86579 828558 86579 10600 NULL 10500 NULL NULL NULL NULL 10501 10600 10700 NULL 1 No NULL N SRS NULL

TI hope to get all your helo

[Vasiliy Zverev · Accepted solution]

Dear Anh, hi!

you should add a condition to get specified option, try this:

select cfv.issue, cfv.*, cfo.*, cf.cfname , issue.pkey
from CustomFieldValue cfv
join CustomField CF on CF.Id = CFV.CustomField
join CustomFieldOption CFO on CF.Id = CFO.CustomField

and cfv.STRINGVALUE = cfo.ID

join jiraissue issue on issue.id = cfv.issue
where (cf.CFName = 'SRS')
AND issue.issuenum = 10565

[Anh Nguyen]

Big thanks, it works for me, just change a bit on the code 

select cfv.issue, cfv.*, cfo.*, cf.cfname , issue.pkey
from CustomFieldValue cfv
join CustomField CF on CF.Id = CFV.CustomField
join CustomFieldOption CFO on (CF.Id = CFO.CustomField and cfv.STRINGVALUE  like cfo.ID)

join jiraissue issue on issue.id = cfv.issue
where (cf.CFName = 'SRS')
AND issue.issuenum = 10565