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

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

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

CodePudding user response：

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;

Demo on db<>fiddle here

CodePudding user response：

• 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: