How are issues and labels associated in the database?

[Gregg Smith]

I am trying to report on issues, including their labels, and I must be looking in the wrong place in the database because what I'm seeing does not make sense.

In the JIRAISSUE table, there are ID (unique identifier) and ISSUENUM (ticket number) fields.  In the LABEL table, there are the ISSUE (presumably a reference to which issue the label belongs) and LABEL (the actual label text) fields.

So, my first questions are "Are the tables listed in the above paragraph the correct tables?" and "Are there any other tables involved in the relationship between Issues and their Labels?"

What seemed simply logical from those tables is that the LABEL.ISSUE field was a reference to which Issue the label applied to.  Looking at the data in the LABEL table (hey, that rhymes!), it appears the ISSUE field contains a reference to the ticket number (ISSUENUM) instead of the unique record identified (ID).  When I look in the JIRAISSUE table, I see that there can be multiple issues with the same ticket number (Issues from different Projects having the same ticket number).  This means, the LABEL.ISSUE field does not correctly identify the exact Issue that the Label is associated with.

So much for the simply logical.  Can anyone please explain how to correctly associate the Labels to the Isssues?

My thanks in advance!

Gregg

[Pieter Versteijnen]

You have to use the jiraissue.id column for this.

select (select concat(pkey,'-',ji.issuenum)
from dbo.project
where id = ji.project) issue
, ji.SUMMARY
, lb.LABEL
from jiraissue as ji
join label as lb on lb.ISSUE = ji.ID

[Nic Brough -Adaptavist-]

Yet another reason to do reporting properly - i.e. NOT with SQL.

Why are you doing this with SQL instead of properly?

But, the issue column on the label table does join with the issue id, not the issuenum.