Create a "flag" table that references unique IDs in 3 different tables-CodePudding

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