IDMS: Using SELECT across a many-to-many structure

Products

• IDMS
• IDMS - Database

Issue/Introduction

This document describes the syntax required to SELECT network records in a many-to-many structure joined by a junction record.

Environment

Release : 19.0
Component : CA IDMS SQL Option

Resolution

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                                     

 

Additional Information

Accessing Network-Defined Databases (with SQL)