Oracle: Is it possible to choose which table to join based on a column value?

Carlos Nunes-Ueno

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.

D Stanley

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.

edited at
0

Comments

0 comments
Login to comment

Related

From Dev

Choose join table from column value

From Dev

Inner Join table based on column value

From Dev

Explain which table to choose "FROM" in a JOIN statement

From Dev

How to join the table based on main table column value in SQL Server?

From Dev

how to get only one record based on column value in oracle join

From Dev

Oracle SQL: Return a string based on which column has the highest value

From Dev

SQL comparison condition column to choose the table to JOIN

From Dev

Join type based on column value

From Dev

MySQL - JOIN based on value of column?

From Dev

Join based on the value to different table

From Dev

In MySQL how to join tables using their names which are based on column values of another table?

From Dev

R - Join on data.table, selecting a different column based on value of another column in row

From Dev

How to conditionally join a table function in Oracle SQL, which has a primary table row column as an argument, without excessive function calls?

From Dev

Choose rows based on two connected column values in one statement - ORACLE

From Dev

Join table with column. How is possible?

From Dev

SQL Max Value of Column to choose which row is selected

From Dev

How to choose a value from another table using a JOIN

From Dev

SQL Adding a Column to table, based on a inner join

From Dev

How to join table based on two column in mysql?

From Dev

Possible to choose table with a wildcard?

From Dev

Is it possible to insert value in table based on parameter value

From Dev

sql-left-outer-join-with rows-based on column value from right table

From Dev

Set column value of a view based on which table I'm selecting from

From Dev

How to find table names which have a same value in other tables based aone column

From Dev

Join Based On Column Value (Best Match)

From Java

'IF' in 'SELECT' statement - choose output value based on column values

From Dev

Conditional join of table based on existence of value

From Dev

MySQL join a different table based on field value

From Dev

Grouping based on value existing in join table

Related Related

  1. 1

    Choose join table from column value

  2. 2

    Inner Join table based on column value

  3. 3

    Explain which table to choose "FROM" in a JOIN statement

  4. 4

    How to join the table based on main table column value in SQL Server?

  5. 5

    how to get only one record based on column value in oracle join

  6. 6

    Oracle SQL: Return a string based on which column has the highest value

  7. 7

    SQL comparison condition column to choose the table to JOIN

  8. 8

    Join type based on column value

  9. 9

    MySQL - JOIN based on value of column?

  10. 10

    Join based on the value to different table

  11. 11

    In MySQL how to join tables using their names which are based on column values of another table?

  12. 12

    R - Join on data.table, selecting a different column based on value of another column in row

  13. 13

    How to conditionally join a table function in Oracle SQL, which has a primary table row column as an argument, without excessive function calls?

  14. 14

    Choose rows based on two connected column values in one statement - ORACLE

  15. 15

    Join table with column. How is possible?

  16. 16

    SQL Max Value of Column to choose which row is selected

  17. 17

    How to choose a value from another table using a JOIN

  18. 18

    SQL Adding a Column to table, based on a inner join

  19. 19

    How to join table based on two column in mysql?

  20. 20

    Possible to choose table with a wildcard?

  21. 21

    Is it possible to insert value in table based on parameter value

  22. 22

    sql-left-outer-join-with rows-based on column value from right table

  23. 23

    Set column value of a view based on which table I'm selecting from

  24. 24

    How to find table names which have a same value in other tables based aone column

  25. 25

    Join Based On Column Value (Best Match)

  26. 26

    'IF' in 'SELECT' statement - choose output value based on column values

  27. 27

    Conditional join of table based on existence of value

  28. 28

    MySQL join a different table based on field value

  29. 29

    Grouping based on value existing in join table

HotTag

Archive