Oracle 9i
I have data in table a, which is my source of truth, which states that each account should have just one plan..
with table_a AS (
( SELECT 7056 AS account_no, 'Mobile Plan Small' plan FROM dual
union all
SELECT 4722 AS account_no, 'BYO Plan' plan FROM dual
)
), table_b AS (
( SELECT 7056 AS account_no, 'Mobile Plan Small' plan FROM dual
UNION ALL
SELECT 7056 AS account_no, 'Mobile Plan Small' plan FROM dual
UNION ALL
SELECT 4722 AS account_no, 'Mobile Medium Small' plan FROM dual
UNION ALL
SELECT 4722 AS account_no, 'BYO Plan' plan FROM dual
)
I'm looking for a result that filters out duplicate records in table b. For instance, in account 7056 table b, a row looked to be repeated in table a, thus I'd want to maintain one of those rows.
And because the account 4722 row with 'Mobile Medium Small' table b is not in table a, I'd want to preserve it.
This my expected output. Thank you
