When mapping associated objects to a relational data model, the driver exposes one-to-one and one-to-many relationships as foreign key relationships. However, many-to-many relationships are handled differently. For many-to-many relationships, the driver exposes a join table to show associations between objects. For example, suppose the Oracle Service Cloud data model contains an EMPLOYEE object and a DEPARTMENT object that are related.
EMPLOYEE (ID, NAME, HIREDATE)
The DEPARTMENT object contains a list of fields and takes the form:
DEPARTMENT (ID, NAME, PROJECTS)
Suppose there is an association between the EMPLOYEE object and the DEPARTMENT object. EMPLOYEES can belong to multiple DEPARTMENTS and DEPARTMENTS can contain multiple EMPLOYEES. In this instance, the driver exposes the following join table using the <objectname>_<associationname> pattern:
EMPLOYEE_DEPARTMENT (EMPLOYEE_ID, DEPARTMENT_ID)
The following query retrieves the name and ID of all the employees who work in the Mailroom:
SELECT EMPLOYEE.ID, EMPLOYEE.NAME
FROM EMPLOYEE, EMPLOYEE_DEPARTMENT, DEPARTMENT
WHERE EMPLOYEE.ID = EMPLOYEE_DEPARTMENT.EMPLOYEE_ID
AND EMPLOYEE_DEPARTMENT.DEPARTMENT_ID = DEPARTMENT.ID
AND DEPARTMENT.NAME = 'Mailroom'
