I have a column within a table that shows a value "key" from Database 1. That "key" value is also present in Database 2 and I want to introduce a new column that shows the "status" of each "key" value".
May idea was to create a query "getStatus" to retrieve each status per key. But how would I make this happen?
To give you more details:
I am building a web app that basically provides the agenda for a JourFixe with my PO. The use case is that I need a tool / app that keeps track / displays status and progress of issues that are living in 2 different systems:
I have "cases" living in Salesforce that are opened from my internal stakeholders. Everything that we need 2nd and 3rd Level support for, we escalate those issues using
Jira, where they exist as "tickets"
So to know which (Salesforce) case is currently "Escalated" (so which case needs to be tracked in the JourFixe) I am regularly dumping / updating the cases into a Retool Database table "SF cases". This DB table holds the following fields:
Ensure that the primary key from the sf_cases table is present in the vulcan_link table as a foreign key when joining both tables.
SELECT
sf.image AS sfImage,
sf.caseId AS caseId,
sf.caseName AS caseName,
sf.vulcanLink AS vulcanLink,
vl.status AS vulcanStatus
FROM
sf_cases AS sf
LEFT JOIN
vulcan_link AS vl
ON
sf.vulcanLink = vl.key
ORDER BY
sf.caseId;
I'm afraid I don't really understand the task to introduce the foreign key. You are saying:
Ensure that the primary key from the sf_cases table is present in the vulcan_link table as a foreign key
BUT:
There is a 1:n relationship from the primary Key ("Key") within the vulcanTickets table. And not vice versa. So that means, that in theory 1 "Key" can be linked to multiple "Case Numbers" from the SFCases table.
Note:
I have tried to set up the primary Key from "vulcanTickets" table as foreign key within the "SFCases" table (so basically the other way around) but run into the following error: