How Can I Link Jira Issues to Organizations If There's No Foreign Key?

Question

The organization_id column in the ORGANIZATION table doesn't act as a foreign key in the ISSUE table. As a result, I can't directly join these tables to identify which organizations are linked to specific issues. How can I link Jira issues to their corresponding organizations?

Environment

Connector: Jira

Answer

We don't store organization information directly in the ISSUE table. Instead, we store this information separately in a custom field as an array in the ISSUE_MULTISELECT_HISTORY table.

To link issues to their associated organizations, do the following:

1. Query the FIELD table to find the custom field ID used for organization values.
2. Use this ID to query the ISSUE_MULTISELECT_HISTORY table and identify rows that contain organization values.
3. Join these results back to the ISSUE table using the issue_id column.
4. Join the result with the ORGANIZATION table using the organization IDs to retrieve organization details.

This approach allows you to link Jira issues to their organizations, even without a direct foreign key.