I have two tables. Table Main and Sub. I need to join these two tables. The key's that have same grp_id is one single group. eg : in Table main (BWA,ST,FD62E015) is one group and (BWA,VI,FD62E015) is other group and so on. The same goes with the other table sub as well. Now i want to join these two tables and get the grp_id from main table in a way that if the group that has key (BWA,FD62E015) in table sub gets the grp_id 1 and 2 from Main table and the group that has key (BWA,FM62Q011) gets grp_id 3 and 4.

So the normal joins wont work here since both the group in the sub table has the key BWA. Is there way to aggregate the key's and join them ?

Table : Main 
Std_id  Grp_id  Key
1234    1       BWA            
1234    1       ST              
1234    1       FD62E015
1234    2       BWA
1234    2       VI
1234    2       FD62E015
1234    3       BWA
1234    3       ST
1234    3       FM62Q011
1234    4       BWA
1234    4       VI
1234    4       FM62Q011

Table : Sub

Std_id  Grp_id  Key
1234    1       BWA
1234    1       FD62E015
1234    2       BWA
1234    2       FM62Q011

Desired Result :

Std_id  sub.Grp_id  main.grp_id sub.Key
1234    1              1          BWA
1234    1              1         FD62E015
1234    1              2          BWA
1234    1              2         FD62E015
1234    2              3          BWA
1234    2              3         FM62Q011
1234    2              4         BWA
1234    2              4         FM62Q011

CodePudding user response：

After you posted desired result, see if this helps.

SQL> with
  2  tmain as
  3    (select std_id, grp_id, listagg(key, ',') within group (order by key) keys
  4     from main
  5     where (std_id, key) in (select std_id, key from sub)
  6     group by std_id, grp_id
  7    ),
  8  tsub as
  9    (select std_id, grp_id, listagg(key, ',') within group (order by key) keys
 10     from sub
 11     group by std_id, grp_id
 12    )
 13  select s.std_id, s.grp_id, tm.grp_id main_grp_id, s.key
 14  from sub s join tsub ts on ts.std_id = s.std_id and ts.grp_id = s.grp_id
 15             join tmain tm on tm.std_id = ts.std_id and tm.keys = ts.keys
 16  order by s.std_id, s.grp_id, tm.grp_id, s.key;

    STD_ID     GRP_ID MAIN_GRP_ID KEY
---------- ---------- ----------- --------
      1234          1           1 BWA
      1234          1           1 FD62E015
      1234          1           2 BWA
      1234          1           2 FD62E015
      1234          2           3 BWA
      1234          2           3 FM62Q011
      1234          2           4 BWA
      1234          2           4 FM62Q011

8 rows selected.

SQL>

• tmain and tsub CTEs make new keys (using listagg function)
• those new keys are then used in the "main" select statement to join those tables (along with the "original" sub table, to get other values you need

CodePudding user response：

Option 1:

SELECT std_id,
       grp_id,
       key
FROM   (
  SELECT m.*,
         COUNT(DISTINCT m.key) OVER (PARTITION BY m.std_id, m.grp_id) AS num_keys
  FROM   main m
         INNER JOIN sub s
         ON (   m.std_id = s.std_id
            AND CEIL(m.grp_id/2) = s.grp_id -- or some other way of mapping the grp_ids
            AND m.key    = s.key)
)
WHERE  num_keys = 2;

Option 2:

You can create a user-defined collection type:

CREATE TYPE string_list AS TABLE OF VARCHAR2(20);

and then use:

SELECT m.std_id,
       m.grp_id,
       m.key
FROM   (SELECT m.*,
               CAST(
                 COLLECT(key) OVER(PARTITION BY std_id, grp_id)
                 AS string_list
               ) AS keys
        FROM   main m
       ) m
       INNER JOIN
       (SELECT s.*,
               CAST(
                 COLLECT(key) OVER(PARTITION BY std_id, grp_id)
                 AS string_list
               ) AS keys
        FROM   sub s
       ) s
       ON (    m.std_id = s.std_id
           AND CEIL(m.grp_id/2) = s.grp_id -- or some other way of mapping the grp_ids
           AND m.key    = s.key
           AND CARDINALITY(m.keys MULTISET INTERSECT DISTINCT s.keys) >= 2
          )

Which, for the sample data:

CREATE TABLE main (Std_id, Grp_id, Key) AS
SELECT 1234, 1, 'BWA' FROM DUAL UNION ALL            
SELECT 1234, 1, 'ST' FROM DUAL UNION ALL             
SELECT 1234, 1, 'FD62E015' FROM DUAL UNION ALL
SELECT 1234, 2, 'BWA' FROM DUAL UNION ALL
SELECT 1234, 2, 'VI' FROM DUAL UNION ALL
SELECT 1234, 2, 'FD62E015' FROM DUAL UNION ALL
SELECT 1234, 3, 'BWA' FROM DUAL UNION ALL
SELECT 1234, 3, 'ST' FROM DUAL UNION ALL
SELECT 1234, 3, 'FM62Q011' FROM DUAL UNION ALL
SELECT 1234, 4, 'BWA' FROM DUAL UNION ALL
SELECT 1234, 4, 'VI' FROM DUAL UNION ALL
SELECT 1234, 4, 'FM62Q011' FROM DUAL;

CREATE TABLE Sub (Std_id, Grp_id, Key) AS
SELECT 1234, 1, 'BWA' FROM DUAL UNION ALL
SELECT 1234, 1, 'FD62E015' FROM DUAL UNION ALL
SELECT 1234, 2, 'BWA' FROM DUAL UNION ALL
SELECT 1234, 2, 'FM62Q011' FROM DUAL;

Both output:

STD_ID	GRP_ID	KEY
1234	1	BWA
1234	1	FD62E015
1234	2	BWA
1234	2	FD62E015
1234	3	BWA
1234	3	FM62Q011
1234	4	BWA
1234	4	FM62Q011

db<>fiddle here

CodePudding user response：

Using [NOT] EXISTS

select *
from  main m
where EXISTS (
    select 1 
    from (select distinct Grp_id from Sub) s
    where NOT EXISTS ( 
         select 1 
         from  Sub s2
         where s2.Grp_id = s.Grp_id and NOT EXISTS ( 
             select 1 
             from  main m2
             where m2.Grp_id = m.Grp_id and s2.Key = m2.Key
             )
         )
    );

db<>fiddle