I currently have a table like this:

The problem I'm having is I'd like to insert all missing PLANNED, DOING, DONE for every set of FK_ID's into the table so the result would be

I had the idea to create a table, although I'm sure a temp table would be fine that would just be

and then do something with OUTER LEFT JOIN and/or NOT IN's but I'm not getting what I'm looking for. I'm thinking I need to have a query with a subquery with those but SQL guy I am not. Here's what I have so far:

SELECT e.*
FROM ActualData e
OUTER LEFT JOIN TempTable i
ON e.Name = i.Name WHERE i.Name IS NULL

This does give me some data, but only the ones that don't appear in ANY of the records of the ActualData table.

CodePudding user response：

You may use partitioned join with static list on status column to generate rows with missing statuses for each partition column (fk_id).

with test_tab(FK_ID, Name, Updated_Date) as (
select 1, 'PLANNED', '[Some Date]' from dual union all
select 1, 'DOING', '[Some Date]' from dual union all
select 2, 'PLANNED', '[Some Date]' from dual union all
select 2, 'DOING', '[Some Date]' from dual union all
select 2, 'DONE', '[Some Date]' from dual union all
select 3, 'PLANNED', '[Some Date]' from dual
)
select
  a.fk_id,
  b.column_value as name,
  a.updated_date
from table(sys.odcivarchar2list(
    'PLANNED',
    'DOING',
    'DONE'
  )) b
  left join test_tab a
    partition by (fk_id)
  on a.name = b.column_value

FK_ID | NAME    | UPDATED_DATE
----: | :------ | :-----------
    1 | DOING   | [Some Date] 
    1 | DONE    | null        
    1 | PLANNED | [Some Date] 
    2 | DOING   | [Some Date] 
    2 | DONE    | [Some Date] 
    2 | PLANNED | [Some Date] 
    3 | DOING   | null        
    3 | DONE    | null        
    3 | PLANNED | [Some Date]

db<>fiddle here

CodePudding user response：

One approach could be to use a cross join to get all the combinations, and then remove those that already exist:

SELECT fk_id, t.name
FROM   ActualData
CROSS JOIN temp_table t
MINUS
SELECT fk_id, name
FROM   ActualData

Of course, this query can be used to insert the "missing" rows back into ActualData:

INSERT INTO ActualData (fk_id, name)
SELECT fk_id, t.name
FROM   ActualData
CROSS JOIN temp_table t
MINUS
SELECT fk_id, name
FROM   ActualData