Home > Back-end >  Create a "flag" table that references unique IDs in 3 different tables
Create a "flag" table that references unique IDs in 3 different tables

Time:07-30

Oracle SQL

I have account IDs in 3 different tables (one column per table). They sometimes repeat within the table's column. Some of the distinct account IDs in Table 1 also appear in Table 2. Some appear in all 3 tables. There is no mutual exclusivity requirement across the tables (i.e - an account ID can appear in any combination of the 3 tables).

For example:

Account ID (from Table 1) Account ID (from Table 2) Account ID (from Table 3)
123451 876495 456123
456123 943215 876495
876495

I'm trying to create a "flag table" like the following, that indicates the tables that each account ID appears in:

Account ID (UNION) Account ID in Table 1? Account ID in Table 2? Account ID in Table 3?
123451 1 0 0
456123 1 0 1
876495 1 1 1
943215 0 1 0

No preference regarding the boolean returning a zero or NULL.

CodePudding user response:

You can use a FULL OUTER JOIN:

SELECT COALESCE(t1.account_id, t2.account_id, t3.account_id) AS account_id,
       NVL2(t1.account_id, 1, 0) AS in_table1,
       NVL2(t2.account_id, 1, 0) AS in_table2,
       NVL2(t3.account_id, 1, 0) AS in_table3
FROM   table1 t1
       FULL OUTER JOIN table2 t2
       ON (t1.account_id = t2.account_id)
       FULL OUTER JOIN table3 t3
       ON (  t1.account_id = t3.account_id
          OR t2.account_id = t3.account_id)

Which, for the sample data:

CREATE TABLE table1 (account_id) AS
SELECT 123451 FROM DUAL UNION ALL
SELECT 456123 FROM DUAL UNION ALL
SELECT 876495 FROM DUAL;

CREATE TABLE table2 (account_id) AS
SELECT 943215 FROM DUAL UNION ALL
SELECT 876495 FROM DUAL;

CREATE TABLE table3 (account_id) AS
SELECT 456123 FROM DUAL UNION ALL
SELECT 876495 FROM DUAL;

Outputs:

ACCOUNT_ID IN_TABLE1 IN_TABLE2 IN_TABLE3
123451 1 0 0
456123 1 0 1
876495 1 1 1
943215 0 1 0

db<>fiddle here

  • Related