# One to Many

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.

# 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:


E01 Johnny Appleseed M02
E02 Erin Macklemore M01
E03 Colby Paperwork M03
E04 Ron Sonswan M01


M01 Loud McQueen
M02 Bossy Pants
M03 Barrel Jones

# 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:

E02 Erin Macklemore
E04 Ron Sonswan

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' ;

M03 Barrel Jones

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.

# 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.