This qes is about oracle db.

I have a table (T1) with several columns:

And another table (T2) with 2 columns:

The Id in this table (t2) is unique. the connection between the two tables is by the "id" column.

I need to check for each (!) Group in T1 (A, B, C, D), which of the "id" from T2 - not found for this group in T1, and the result needs to be: group id (that does not exist in this group)

for the example on the above tables, I expected to get this result:

Thank You!

CodePudding user response：

The idea here is to create all possible record combinations by using a Cartesian join and then match all groups and select non existing via left join and not null

create table T1 (c1 varchar2(10), c2 number(10), c3 number(10))

 insert into T1 values('A',     1,  50);
 insert into T1 values('A',     5,  40);
 insert into T1 values('B',     4 , 54);
 insert into T1 values('C',     1,  33);
 insert into T1 values('C', 6,  33);
 insert into T1 values('D',     5,  13);
 insert into T1 values('D',     3,  4);

create table T2 (c6 number(10), c7 varchar2(10))

 insert into T2 values( 1,  'aaa');
 insert into T2 values( 2,  'bbb');
 insert into T2 values( 3,  'ccc');
 insert into T2 values( 4,  'ddd');
 insert into T2 values( 5,  'eee');
 insert into T2 values( 6,  'fff');
 insert into T2 values( 7,  'ggg');

 SELECT tx.c1, tx.c6, ty.c1
 FROM
     (select c1, c6 
      from 
          (select distinct c1 from T1) ta,
          (select distinct c6 from T2) tb) tx -- tx is the cartisian product
          left join 
          (select c1, c2 from T1 group by c1, c2) ty 
              on tx.c1 = ty.c1 and tx.c6 = ty.c2
WHERE 
    ty.c1 is null
ORDER BY 1, 2

This gets you exactly what you're looking for. Tested and verified

CodePudding user response：

First, find all the possible combinations, then determine which of them don't exist:

WITH cteCombinations
 AS (SELECT DISTINCT T1."group", T2.ID
       FROM T1
       CROSS JOIN T2)
SELECT c."group", c.ID
  FROM cteCombinations c
  LEFT OUTER JOIN T1
    ON T1.ID = c.ID AND
       T1."group" = c."group"
  WHERE T1.ID IS NULL
  ORDER BY c."group", c.ID

The CTE (Common Table Expression) uses a CROSS JOIN to find all of the unique combinations of group and ID; then the LEFT OUTER JOIN is used to determine which of the combinations don't exist in T1.

Another way to do it is:

WITH cteCombinations
 AS (SELECT DISTINCT T1."group", T2.ID
       FROM T1
       CROSS JOIN T2)
SELECT c."group", c.ID
  FROM cteCombinations c
  WHERE (c."group", c.ID) NOT IN (SELECT "group", ID
                                    FROM T1)
  ORDER BY c."group", c.ID

Here we use the same CTE to generate the possible combinations, but instead of a LEFT OUTER JOIN we use a NOT IN comparison to determine which of the combinations are not present in table T1.

db<>fiddle here

CodePudding user response：

In Oracle you may use partitioned join

with a(grp, id, price) as (
  select 'A', 1, 50 from dual union all
  select 'A', 5, 40 from dual union all
  select 'B', 4, 54 from dual union all
  select 'C', 1, 33 from dual union all
  select 'C', 6, 33 from dual union all
  select 'D', 5, 13 from dual union all
  select 'D', 3, 4 from dual
)
, b (id, descr) as (
  select 1, 'aaa' from dual union all
  select 2, 'bbb' from dual union all
  select 3, 'ccc' from dual union all
  select 4, 'ddd' from dual union all
  select 5, 'eee' from dual union all
  select 6, 'fff' from dual union all
  select 7, 'ggg' from dual
)
select
  grp
  , id
from a
  partition by (grp)
  right join b
    using (id)
where a.price is null
order by grp, id

GRP | ID
:-- | -:
A   |  2
A   |  3
A   |  4
A   |  6
A   |  7
B   |  1
B   |  2
B   |  3
B   |  5
B   |  6
B   |  7
C   |  2
C   |  3
C   |  4
C   |  5
C   |  7
D   |  1
D   |  2
D   |  4
D   |  6
D   |  7

db<>fiddle here