How to check if value must exist in another table?

Question

I have a scenario where reference table ref has a column with different values. I need all those values to be available in other table, can you help with a query?

ref table:

description
----------
abc
bcd
cdf

data table:

id | Description
-------------------
123| abc
123| bcd
123| cdf
124| abc
124| bcd

output should be:

123| abc
123| bcd
123| cdf

I am able to get the matching entries, but I need only those entries that matches with all values.

Answer 1

You can build a "key" to join on by combining all of the values for each ID in the DATA table and compare that to a "key" containing all of the values from the REF table.

WITH
    data (id, description)
    AS
        (SELECT 123, 'abc' FROM DUAL
         UNION ALL
         SELECT 123, 'bcd' FROM DUAL
         UNION ALL
         SELECT 123, 'cdf' FROM DUAL
         UNION ALL
         SELECT 124, 'abc' FROM DUAL
         UNION ALL
         SELECT 124, 'bcd' FROM DUAL),
    REF (description)
    AS
        (SELECT 'abc' FROM DUAL
         UNION ALL
         SELECT 'bcd' FROM DUAL
         UNION ALL
         SELECT 'cdf' FROM DUAL)
SELECT id, description
  FROM (SELECT id,
               description,
               LISTAGG (description, '|')
                   WITHIN GROUP (ORDER BY description)
                   OVER (PARTITION BY id)    AS djoinkey
          FROM data d)
 WHERE djoinkey =
       (SELECT LISTAGG (description, '|') WITHIN GROUP (ORDER BY description) AS rjoinkey FROM REF);


    ID    DESCRIPTION
______ ______________
   123 abc
   123 bcd
   123 cdf

Answer 2

Create a cross join between the unique IDs in the data and the list of reference values, then left join that result with the actual data. If the count of matching rows is the same as the count of rows, then that id has each of the reference values.

create table #ref (val nvarchar(3));

insert into #ref (val)
values
('abc'),
('bcd'),
('cdf');

create table #dat (id int, val nvarchar(3));

insert into #dat (id,val)
values
(123,'abc'),
(123,'bcd'),
(123,'cdf'),
(124,'abc'),
(124,'bcd');


SELECT #dat.id, #dat.val
FROM #dat
    INNER JOIN (
                SELECT ids_ref.id, COUNT(*) AS vals, SUM(CASE WHEN #dat.val IS NOT NULL THEN 1 ELSE 0 END) AS dat_vals
                FROM (SELECT ids.id, #ref.val FROM (SELECT DISTINCT id FROM #dat) ids, #ref) ids_ref
                    LEFT JOIN #dat ON ids_ref.id = #dat.id AND ids_ref.val = #dat.val
                GROUP BY ids_ref.id
                ) chk ON #dat.id = chk.id
WHERE chk.vals = chk.dat_vals;
    
drop table #ref;
drop table #dat;