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).
Dig deeper on Oracle and SQL
Related Q&A from Rudy Limeback, SQL Consultant, r937.com
Read an example of an SQL case expression from our SQL expert Rudy Limeback.continue reading
Read SQL expert Rudy Limeback's advice for counting combinations in a table with SQL's GROUP BY clausecontinue reading
Read about the Mimer Validator, a tool used to verify your SQL code, in this tip from SQL expert Rudy Limeback.continue reading
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.