First of all, this isn't my design, and I don't have the option of changing it. I've got three tables that define a relationship between action items and incidents and audits. The tables, in very simplified form, are set up like this:
ACTION_ITEMS
-------
ID SOURCE_ID SOURCE_TYPE
1 12345 INC
2 67890 AUD
INCIDENTS
-------
ID
12345
AUDITS
-------
ID
67890
The SOURCE_TYPE column indicates which of the other two tables is relevant to that row. It's simple enough to get the details for all the records by creating separate queries for the incident and audit action items, joining those to the appropriate tables, and then performing a union.
What I'd like to know is if it's possible to conditionally join to a table based on the value of a column. In other words, something like this, but that actually works:
SELECT
AI.*
,INC.*
,AUD.*
FROM ACTION_ITEMS AI
JOIN
CASE AI.SOURCE_TYPE
WHEN 'INC' THEN INCIDENTS INC ON (AI.SOURCE_ID = INCIDENTS.ID)
WHEN 'AUD' THEN AUDITS AUD ON (AI.SOURCE_ID = AUDITS.ID)
END;
**Edited to clarify that I'm seeking to retrieve data from all tables.
No, but you can join both tables and use the condition in your join clause:
SELECT AI.*
FROM ACTION_ITEMS AI
LEFT JOIN INCIDENTS ON AI.SOURCE_TYPE = 'INC' AND (AI.SOURCE_ID = INCIDENTS.ID)
LEFT JOIN AUDITS ON AI.SOURCE_TYPE = 'AUD' AND (AI.SOURCE_ID = INCIDENTS.ID)
Note that you will be able to access columns from either table depending on the SOURCE_TYPE
- if there's no match in the right-hand table those values will be NULL
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments