Getting started with MySQL Limit and Offset Creating databases Backup using mysqldump Table Creation INSERT Error codes Stored routines (procedures and functions)Reserved Words String operations DELETE VIEW Indexes and Keys Date and Time Operations Comment Mysql LOAD DATA INFILE PREPARE Statements ALTER TABLE Joins UPDATE Change Password JSON MySQL Admin TRIGGERS Pivot queries Configuration and tuning Converting from MyISAM to InnoDB SELECT Create New User Group By UNION Drop Table Data Types Performance Tuning Events ENUM Install Mysql container with Docker-Compose Arithmetic Character Sets and Collations MyISAM Engine Many-to-many Mapping table Using Variables Log files Partitioning Clustering Security via GRANTs Backticks mysqlimport MySQL LOCK TABLE MySQL Unions ORDER BY MySQL client Mysql Performance Tips Temporary Tables Transaction Customize PS1 Dealing with sparse or missing data Dynamic Un-Pivot Table using Prepared Statement NULL Replication Connecting with UTF-8 Using Various Programming language.SSL Connection Setup Handling Time Zones Time with subsecond precision Error 1055: ONLY_FULL_GROUP_BY: something is not in GROUP BY clause ...Full-Text search Extract values from JSON type Regular Expressions Recover and reset the default root password for MySQL 5.7+One to Many JOINS: Join 3 table with the same name of id.Server Information Recover from lost root password

One to Many

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

EMP_ID	FIRST_NAME	LAST_NAME	MGR_ID
E01	Johnny	Appleseed	M02
E02	Erin	Macklemore	M01
E03	Colby	Paperwork	M03
E04	Ron	Sonswan	M01

MANAGERS

MGR_ID	FIRST_NAME	LAST_NAME
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:

EMP_ID	FIRST_NAME	LAST_NAME
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' ;

MGR_ID	FIRST_NAME	LAST_NAME
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.

Contributors

Topic Id: 9600

Example Ids: 29650,29651,29652

This site is not affiliated with any of the contributors.