JIRA importers from Mantis, Why is jiraJOIN tables jiraissue and customfieldvalue

avidD

I have imported issues from Mantis using the Importers plugin. Now I am trying to do some cleanup using SQL on the issue database. The issue I am currently working on is, that the Mantis categories have been imported Components and all Issues were imported as Bugs. I now want to write a little update script that updates the issue types based on components and for some individual issues based on the external issue IDs. I am starting by joining the 'jiraissue' and 'customfieldvalue' on ID where the External issue ID is the External issue ID. This is my query:

SELECT j.ID AS JiraID, f.ID AS JiraID2, CONVERT(int, f.STRINGVALUE) AS MantisID
FROM jiraissue j RIGHT OUTER JOIN customfieldvalue f ON j.ID = f.ID
WHERE 
  CUSTOMFIELD = 10317

However, it turns out that there are entries in 'jiraissue' only for very few entries in the customfieldvalue table. Am I joining with the wrong table?

Nic Brough -Adaptavist-

I think you're joining the correct table. I'm no DBA, but to do "get custom field value from an issue", the most primitive statement is

select * from customfieldvalue where issue = <issue id from jiraissue table> and customfield = <customfield id from customfield table>

There should be no customfieldvalue entries without a matching jiraissue record - the whole point of the customfieldvalue table is that it holds data for the issues, so if the issues are missing, the implication is broken data! (Of course, it's fine to have jiraissue records with no customfieldvalues).