CODEWITHSUNDEEP
mysqlsql
yokoyoko
yokoyoko

Reputation: 265

Declare multiple variables for single variable in mysql

My query pulls list of bug items by jira issue (goes specific to component of jira), I need to find out how can i enter multiple pkey in this query, single pkey works fine, however when I declare multiple pkey it throws an error message.

In following query, abc-123 is a jira bug id, subquery finds component name and 2nd subquery will fetch project name, remaining query will pull list of bugs associated with the component, I would like to enter 'def-456', 'jdk-985' next to 'abc-123' , I tried setting new variable however it did not work, can someone please help

$ 

set @pkey := 'abc-123';

select jiraissue.*, co.* 
from jiraissue,project,issuetype,nodeassociation,component,
customfieldvalue cv
,customfieldoption co 
where 
component.cname = (SELECT component.cname
FROM nodeassociation, component, jiraissue
WHERE component.ID = nodeassociation.SINK_NODE_ID
AND jiraissue.id = nodeassociation.SOURCE_NODE_ID
AND nodeassociation.ASSOCIATION_TYPE = 'IssueComponent'
AND pkey = @pkey) and 
project.pkey = (SELECT substring_index(jiraissue.pkey,'-',1) as project_name
FROM nodeassociation, component, jiraissue
WHERE component.ID = nodeassociation.SINK_NODE_ID
AND jiraissue.id = nodeassociation.SOURCE_NODE_ID
AND nodeassociation.ASSOCIATION_TYPE = 'IssueComponent'
AND pkey = @pkey) and 
issuetype.pname = 'Bug' and
jiraissue.project = project.id and 
jiraissue.issuetype = issuetype.id and 
nodeassociation.association_type = 'IssueComponent' and
nodeassociation.source_node_entity = 'Issue'  and
nodeassociation.source_node_id = jiraissue.id  and
nodeassociation.sink_node_entity = 'Component'  and
nodeassociation.sink_node_id = component.id 
and jiraissue.id = cv.issue
and cv.stringvalue = co.id
and cv.customfield = 10020;

Upvotes: 0

Views: 888

Answers (1)

CognitiveCarbon
CognitiveCarbon

Reputation: 191

Try replacing

AND pkey = @pkey

with this:

AND pkey in (@pkey, @pkey1, @pkey2)

and then at the top:

set @pkey := 'abc-123', @pkey1 := 'def-456', @pkey2 = 'ghi-789'

Upvotes: 1

Related Questions