This document describes the syntax required to SELECT network records in a many-to-many structure joined by a junction record.
Release : 19.0
Component : CA IDMS SQL Option
In this example, there is a many-to-many relationship between the EMPLOYEE and JOB records, with the EMPOSITION record being the junction between the two.
Assume the SELECT needs to list all of the JOB records to which the EMPLOYEE with ID 3 are related.
This method uses traditional SQL:
SET SESSION CURRENT SCHEMA EMPNET;
*+ Status = 0 SQLSTATE = 00000
SELECT EMP_ID_0415, FINISH_YEAR_0420, JOB_ID_0440, TITLE_0440
FROM EMPLOYEE, EMPOSITION, JOB
WHERE EMP_ID_0415=3 AND "EMP-EMPOSITION" AND "JOB-EMPOSITION";
*+
*+ EMP_ID_0415 FINISH_YEAR_0420 JOB_ID_0440 TITLE_0440
*+ ----------- ---------------- ----------- ----------
*+ 3 0 3001 MGR INTERNL SOFTWARE
*+ 3 1981 3011 DATABASE ADMIN.
*+ 3 1979 3031 DOCUMENTATION SPEC
*+
*+ 3 rows processed
It can also be done with more modern "join" syntax.
SET SESSION CURRENT SCHEMA EMPNET;
*+ Status = 0 SQLSTATE = 00000
SELECT EMP_ID_0415, FINISH_YEAR_0420, JOB_ID_0440, TITLE_0440
FROM EMPLOYEE INNER JOIN EMPOSITION ON "EMP-EMPOSITION"
INNER JOIN JOB ON "JOB-EMPOSITION"
WHERE EMP_ID_0415=3;
*+
*+ EMP_ID_0415 FINISH_YEAR_0420 JOB_ID_0440 TITLE_0440
*+ ----------- ---------------- ----------- ----------
*+ 3 0 3001 MGR INTERNL SOFTWARE
*+ 3 1981 3011 DATABASE ADMIN.
*+ 3 1979 3031 DOCUMENTATION SPEC
*+
*+ 3 rows processed
Accessing Network-Defined Databases (with SQL)