Oracle - Compare two sets of columns are equal

Question

I have an output of a table of a single column with values similar to

OUTPUT
A
B.

And I have a table with a set consisting of an ID and values

ID	Data
1	A
1	B
1	C
2	A
2	B
3	B
3	C
3	D

I'm having trouble to build an oracle query that compares the output of the first table with the data column of the second table, and let me know which id matches the exact same data if I were to group them by id.

So for this scenario only ID 2 should be returned since it matches exactly, but ID 1 won't return because it has an extra value

Questions asking for homework help must include a summary of the work you've done so far to solve the problem, and a description of the difficulty you are having solving it. Please read [How to ask homework questions](//meta.stackoverflow.com/q/334822) and [edit] your post. — Ken White, Jan 14 '22 at 05:10
This answer could help: https://stackoverflow.com/questions/23092901/identifying-equivalent-sets-in-sql-server. — tinazmu, Jan 14 '22 at 05:37

score 2 · Accepted Answer · answered Jan 14 '22 at 09:30

2

Left join TableA to TableB.
Then the 100% match will have no unmatched OUTPUT.
And the matched will have the same count as what's in TableA.

SELECT b.ID
FROM TableB b
LEFT JOIN TableA a ON a.OUTPUT = b.Data
GROUP BY b.ID
HAVING COUNT(CASE WHEN a.OUTPUT IS NULL THEN 1 END) = 0
   AND COUNT(DISTINCT a.OUTPUT) = (SELECT COUNT(*) FROM TableA)
ORDER BY b.ID;

ID
2

Demo on db<>fiddle here

answered Jan 14 '22 at 09:30

LukStorms

28,916
5
31
45

This worked for me! My data is slightly different so I made few adjustments, but I was able to work it out with you code as the core. I really appreciate your help – Juan Varguez Jan 14 '22 at 22:08

score 0 · Answer 2 · answered Jan 14 '22 at 08:35

The first select in the with clause is a join of tables.
The second select uses the first select and finds the id whose column "Data" takes all the values from the output column of table A.
The third select contains only the ids without the "Data" in table A.
The result contains only ids that have all outputs and no extra.
Unique values in both tables are prerequisites.
Oracle 11RG2.

DDL:

CREATE TABLE TableA
    ("OUTPUT" varchar2(1))
;


INSERT ALL 
    INTO TableA ("OUTPUT")
         VALUES ('A')
    INTO TableA ("OUTPUT")
         VALUES ('B')
SELECT * FROM dual
;


CREATE TABLE TableB
    ("ID" int, "Data" varchar2(1))
;


INSERT ALL 
    INTO TableB ("ID", "Data")
         VALUES (1, 'A')
    INTO TableB ("ID", "Data")
         VALUES (1, 'B')
    INTO TableB ("ID", "Data")
         VALUES (1, 'C')
    INTO TableB ("ID", "Data")
         VALUES (2, 'A')
    INTO TableB ("ID", "Data")
         VALUES (2, 'B')
    INTO TableB ("ID", "Data")
         VALUES (3, 'B')
    INTO TableB ("ID", "Data")
         VALUES (3, 'C')
    INTO TableB ("ID", "Data")
         VALUES (3, 'D')
SELECT * FROM dual
;

SQL:

with a (id, "Data", output) as (
  select
    id, "Data", output
  from
    tableb left join tablea on "Data"=output
)
  select
    id
  from
    a
  where
    output is not null
  group by id
  having count("Data") = (select count(output) from tablea)
minus
  select id from a where output is null
;

Output:

ID
2

Oracle - Compare two sets of columns are equal

2 Answers2