Assistance with code to extract Assignee

[CostaM]

Good day I posted this question a few days ago but can't seem to locate it, my apologies for posting it again.

We are using this code below to extract the Assignee from Jira but it doesn't return the details required, are there any suggestions as to what else we can use or what may be wrong with this code?

select concat (p.pkey, '-', j.issuenum) AS IssueKey,
	j.summary,
	t.pname as issuetype,
	s.pname as status,
	u.lower_user_name as assignee,
	concat(u.first_name,' ',u.last_name) as "assignee full_name"
from jiraissue j
left join project p on j.project = p.id
left join issuestatus s on s.id = j.issuestatus
left join issuetype t on j.issuetype = t.id
left join app_user a ON j.assignee=a.user_key
left join cwd_user u on u.id=a.id;

Thanks 

[Fabio Racobaldo _Herzum_ · Accepted solution]

Hi @CostaM ,

here the correct SQL :

select concat (p.pkey, '-', j.issuenum) AS IssueKey,
j.summary,
t.pname as issuetype,
s.pname as status,
u.lower_user_name as assignee,
u.display_name as assignee_full_name
from jiraissue j
left join project p on j.PROJECT = p.id
left join issuestatus s on s.id = j.issuestatus
left join issuetype t on j.issuetype = t.id
left join cwd_user u on u.user_name=j.ASSIGNEE;

Hope this helps,

Fabio

[CostaM]

Hi Fabio, 

thanks very much for your suggestion, will try it out and provide feedback.

regards

Costa

[Fabio Racobaldo _Herzum_]

you're welcome @CostaM , please let me know if it works on your side (I tested it on a MySQL DB)

[CostaM]

Hi Fabio, we tested it and it still provided no Assignee, however, we then linked it to the App_User table using the ID as a foreign key and it returned results, 

thanks for your assistance.

[Fabio Racobaldo _Herzum_]

Good! Please mark my answer as accepted so this thread can be considered closed