CODEWITHSUNDEEP
sqloracle-database
Bibz
Bibz

Reputation: 321

How to query data from a second table when the table's name is in first table's column data

I have a weird database schema (that I can't change) where the rows in TableA will contain a table name where more information are stored. I want to query data from TableA and join them with the extended values contained in the table from the queried row.

e.g. assuming i have the following data

TableA
Event ID     Accnt ID    DetailTable
-----------  ----------  ----------------
1            UserA       Table1
2            UserA       Table2
3            UserB       Table2

Table1
Accnt ID    Status
----------  ------------
UserA       Active

Table2
Accnt ID    Status
----------  ------------
UserA       Inactive
UserB       Active

I would like to have the following resulset 

select ... from ...

Result
Accnt ID   DetailTable    Status
---------  -------------  ---------
UserA      Table1         Active
UserA      Table2         Inactive
UserB      Table2         Active

It can probably be done using a loop on each of the record from TableA and then querying the proper detail table, but I was wondering if it was doable in a single Select statement in Oracle SQL.

Thank you

Upvotes: 0

Views: 97

Answers (1)

JNevill
JNevill

Reputation: 50308

This can be a common problem where someone avoids the EAV anti-pattern and jumps into the opposite anti-pattern of having dynamically generated detail tables for every type of object that is stored in a "main" table. When you find yourself storing table or field names as data, then it might be time to rethink your schema. But, like EAV, sometimes it's unavoidable.

If you know the table names already that might appear in your DetailTable column then you could join in those in conditionally, or use a UNION query (assuming the columns match up nicely in your detail table):

SELECT
    TableA.AccntID,
    unionDetail.DetailTable,
    unionDetail.Status
FROM
    TableA
    INNER JOIN 
        (
            SELECT AccntID, Status, 'Table1' as DetailTable FROM Table1
            UNION ALL
            SELECT AccntID, Status, 'Table2' FROM Table2        
        ) AS unionDetail ON
        TableA.AccntID = unionDetail.AccntID AND
        TableA.DetailTable = unionDetail.DetailTable

Of course this all falls apart if the columns don't match up in the detail tables, or if you have a ton of detail tables or they are being dynamically added or removed.

Upvotes: 2

Related Questions