MySQL 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.
Remarks[edit | edit source]
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[edit | edit source]
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:
Get the Employees Managed by a Single Manager[edit | edit source]
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' ;
Ultimately, for every manager we query for, we will see 1 or more employees returned.
Get the Manager for a Single Employee[edit | edit source]
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.