How to Select all possible distinct related "two-ways" values for any given ID from a junc-CodePudding

table TBL_IN "IN Document details table"

IID	IDate	ISubject
I-225
I-300
I-410

table TBL_OUT "OUT Document details table"

OID	ODate	OSubject
O-20
O-35

table TBL_INOUT "IN OUT Document Junction table"

IOID	IID	OID
1	I-225	O-20
2	I-225	O-35
3	I-300	O-35
4	I-410	O-20

Question is how to get the following query result in oracle sql or access query from the junction table?

query QRY_INOUT_Related "Select all possible related "two-ways" values for any given ID"

ID	IO_Related
O-20	I-225,I410,O-35,I-300

ID	IO_Related
I-300	O-35,I-225,O-20,I-410

I hope I clarified the idea as possible as I can.

CodePudding user response：

In Oracle, you appear to want a hierarchical query that finds all the distinct connections via either IID or OID and then want to aggregate:

SELECT id,
       LISTAGG(value, ',') WITHIN GROUP (ORDER BY value) AS io_related
FROM   (
  SELECT DISTINCT id, value
  FROM   (
    SELECT CONNECT_BY_ROOT oid AS id,
           iid,
           oid
    FROM   tbl_inout
    START WITH oid = 'O-20'
    CONNECT BY NOCYCLE
       PRIOR oid = oid
    OR PRIOR iid = iid
  )
  UNPIVOT(value FOR key IN (iid, oid))
  WHERE id <> value
)
GROUP BY id

Outputs:

ID IO_RELATED

O-20 I-225,I-300,I-410,O-35

ID	IO_RELATED
O-20	I-225,I-300,I-410,O-35

and

SELECT id,
       LISTAGG(value, ',') WITHIN GROUP (ORDER BY value) AS io_related
FROM   (
  SELECT DISTINCT id, value
  FROM   (
    SELECT CONNECT_BY_ROOT iid AS id,
           iid,
           oid
    FROM   tbl_inout
    START WITH iid = 'I-300'
    CONNECT BY NOCYCLE
       PRIOR oid = oid
    OR PRIOR iid = iid
  )
  UNPIVOT(value FOR key IN (iid, oid))
  WHERE id <> value
)
GROUP BY id

Outputs:

ID IO_RELATED

I-300 I-225,I-410,O-20,O-35

ID	IO_RELATED
I-300	I-225,I-410,O-20,O-35

db<>fiddle here