How do you put literals into the result a sql result set based on which table was joined to create the row?

Question

I have 3 tables, one which represents a "supertype", with an ID column. Two other tables are each subtypes, with an ID column that is a foreign key to the supertype table, plus a subtype-specific column.

I want a query that returns all the data, as well as a column I can use as a discriminator that tells me what table the row came from.

So, for example, how can I modify this:

SELECT * from SUPER S 
left outer join SUB_1 S1 on S.ID = S1.ID
left outer join SUB_2 S2 on S.ID = S2.ID

Which returns me this:

ID    SUB_COL_1  SUB_COL_2
====  =========  =========
0001  value x    NULL
0002  value y    NULL
0003  NULL       value z

Into something that will add a discriminator column with some hard-coded literal values, like this:

ID    DISCRIMINATOR  SUB_COL_1  SUB_COL_2
====  =============  =========  =========
0001  SUBTYPE_1      value x    NULL
0002  SUBTYPE_1      value y    NULL
0003  SUBTYPE_2      NULL       value z

I am not allowed to modify the data model in any way. I also cannot do any post-processing by programmatically testing for NULLS after the fact. I need to work with the tables as is, and produce the exact result set shown above. I am using Oracle 11g, if that makes any difference to the answer.

Answer 1

Maybe this is what you are looking for...you might have to make a few changes to make it work on oracle.

SELECT case coalesce(SUB_COL_1,'') when '' then 'SUBTYPE_2'  else 'SUBTYPE_1' end,  * from SUPER S 
left outer join SUB_1 S1 on S.ID = S1.ID
left outer join SUB_2 S2 on S.ID = S2.ID

Answer 2

You can add:

CASE IF S1.ID IS NULL THEN 'SUBTYPE_1' ELSE 'SUBTYPE_2' END AS DISCRIMINATOR,

at the start of your SELECT clause.

Answer 3

I usually do this with a UNION query

Select S.ID, SUBTYPE_1 as DISCRIMINATOR, S1field1 as SUB_COL_1, null as SUB_COL_2  
from SUPER S 
join SUB_1 S1 on S.ID = S1.ID
union all 
Select S.ID, SUBTYPE_2 as DISCRIMINATOR, null as SUB_COL_1, S2.field1 as SUB_COL_2  
from SUPER S 
join SUB_2 S2 on S.ID = S2.ID

Answer 4

I'd probably add the identifier to each table's data in a subquery before joining it.

SELECT * from 
    (select *, 'SUPER' as DISCRIMINATOR from SUPER ) S 
left outer join 
    (select *, 'SUBTYPE1' as DISCRIMINATOR from SUB_1 ) S1 
    on S.ID = S1.ID
left outer join 
    (select *, 'SUBTYPE1' as DISCRIMINATOR from SUB_2 ) S2 
    on S.ID = S2.ID