Retrieve data related by composite key
How to retrieve related data which has a composite primary key and corresponding foreign key in the other multiple tables?
As you suggested, related tables are related through their primary and foreign keys. In order to retrieve related data, we simply join the tables on all the columns in their keys:
select dept.DVID as dept_division , dept.DPID as dept_id , sect.SCID as section_id , sect.SCNAME as section_name , count(EMPNO) as staff_count from DEPARTMENT as dept inner join SECTION as sect on dept.DVID = sect.DVID and dept.DPID = sect.DPID left outer join EMPLOYEE as empl on sect.DVID = empl.DVID and sect.DPID = empl.DPID and sect.SCID = empl.SCID group by dept.DVID , dept.DPID , sect.SCID , sect.SCNAME
For additional examples involving these tables, see Composite foreign keys (13 September 2002).