Performing a JOIN on groups of values using nested tables

-- Join on a nested table example:

-- this example takes 2 tables (c & d). On each table it does

-- a group by, and the details of that group are collected into a nested table.

-- tables c & d are then joined on that nested table

-- I probably would never do this, but I do think it is interesting.

--DROP TABLE D CASCADE CONSTRAINTS;

CREATE TABLE d (animal VARCHAR2(5 BYTE), grp NUMBER(6));

SET DEFINE OFF;

INSERT INTO d (animal, grp) VALUES ('cat', 4);

INSERT INTO d (animal, grp) VALUES ('cat', 7);

INSERT INTO d (animal, grp) VALUES ('toad', 7);

INSERT INTO d (animal, grp) VALUES ('frog', 4);

INSERT INTO d (animal, grp) VALUES ('frog', 7);

COMMIT ;

-- DROP TABLE c CASCADE CONSTRAINTS;

CREATE TABLE c( animal VARCHAR2(5 BYTE), grp NUMBER(6));

SET DEFINE OFF;

INSERT INTO c (animal, grp) VALUES ('cat', 1);

INSERT INTO c (animal, grp) VALUES ('frog', 1);

INSERT INTO c (animal, grp) VALUES ('frog', 66);

INSERT INTO c (animal, grp) VALUES ('cat', 33);

INSERT INTO c (animal, grp) VALUES ('cat', 44);

INSERT INTO c (animal, grp) VALUES ('cat', 66);

INSERT INTO c (animal, grp) VALUES ('frog', 44);

INSERT INTO c (animal, grp) VALUES ('toad', 44);

COMMIT ;

SELECT grp, animal FROM c ORDER BY grp, animal;

SELECT grp, animal FROM d ORDER BY grp, animal;

CREATE OR REPLACE TYPE string20_table IS TABLE OF VARCHAR2(20);

DECLARE

mystr VARCHAR2(80);

BEGIN

FOR cur1 IN

(SELECT *

FROM (SELECT grp grp1, CAST(COLLECT(animal) AS string20_table) tab1

FROM c GROUP BY grp)

JOIN

(SELECT grp grp2, CAST(COLLECT(animal) AS string20_table) tab2

FROM d GROUP BY grp)

ON tab1 = tab2

)

LOOP

DBMS_OUTPUT.put_line( TO_CHAR(cur1.grp1)

|| '='

|| TO_CHAR(cur1.grp2));

END LOOP;

END;

-- final output

-- 1=4

-- 44=7

-- 66=4