One to Many

Introduction#

The idea of one to many (1:M) concerns the joining of rows to each other, specifically cases where a single row in one table corresponds to many rows in another.

1:M is one-directional, that is, any time you query a 1:M relationship, you can use the ‘one’ row to select ‘many’ rows in another table, but you cannot use a single ‘many’ row to select more than a single ‘one’ row.

Remarks#

For most cases, working with a 1:M relationship requires us to understand Primary Keys and Foreign Keys.

A Primary key is a column in a table where any single row of that column represents a single entity, or, selecting a value in a primary key column results in exactly one row. Using the above examples, an EMP_ID represents a single employee. If you query for any single EMP_ID, you will see a single row representing the corresponding employee.

A Foreign Key is a column in a table that corresponds to the primary key of another different table. From our example above, the MGR_ID in the EMPLOYEES table is a foreign key. Generally to join two tables, you’ll join them based on the primary key of one table and the foreign key in another.

Example Company Tables

Consider a company where every employee who is a manager, manages 1 or more employees, and every employee has only 1 manager.

This results in two tables:

EMPLOYEES

MANAGERS

Get the Employees Managed by a Single Manager

SELECT e.emp_id , e.first_name , e.last_name FROM employees e INNER JOIN managers m ON m.mgr_id = e.mgr_id WHERE m.mgr_id = 'M01' ;

Results in:

Ultimately, for every manager we query for, we will see 1 or more employees returned.

Get the Manager for a Single Employee

Consult the above example tables when looking at this example.

SELECT m.mgr_id , m.first_name , m.last_name FROM managers m INNER JOIN employees e ON e.mgr_id = m.mgr_id WHERE e.emp_id = 'E03' ;

As this is the inverse of the above example, we know that for every employee we query for, we will only ever see one corresponding manager.