MySQL One-To-Many & Many-to-Many Cheatsheet

Here's a simple example of implementing one-to-many & many-to-many relationships between database tables.
I was using MySQL for making these queries.

Enjoy it! (:

Creating DB and Tables

Creating New Database

create database company;
use company;

Creating Database Schema

create table cats (id int primary key auto_increment, name varchar(255) not null);
create table projects (id int primary key auto_increment, name varchar(255) not null, cat_id int not null, foreign key (cat_id) references cats(id));
create table persons (id int primary key auto_increment, name varchar(255) not null);
create table ptable (person_id int not null, project_id int not null, primary key (person_id, project_id), foreign key (person_id) references persons(id), foreign key (project_id) references projects(id));

Viewing Database Schema

describe cats;
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int(11)      | NO   | PRI | NULL    | auto_increment |
| name  | varchar(255) | NO   |     | NULL    |                |
+-------+--------------+------+-----+---------+----------------+

describe projects;
+--------+--------------+------+-----+---------+----------------+
| Field  | Type         | Null | Key | Default | Extra          |
+--------+--------------+------+-----+---------+----------------+
| id     | int(11)      | NO   | PRI | NULL    | auto_increment |
| name   | varchar(255) | NO   |     | NULL    |                |
| cat_id | int(11)      | NO   | MUL | NULL    |                |
+--------+--------------+------+-----+---------+----------------+

describe persons;
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int(11)      | NO   | PRI | NULL    | auto_increment |
| name  | varchar(255) | NO   |     | NULL    |                |
+-------+--------------+------+-----+---------+----------------+

describe ptable;
+------------+---------+------+-----+---------+-------+
| Field      | Type    | Null | Key | Default | Extra |
+------------+---------+------+-----+---------+-------+
| person_id  | int(11) | NO   | MUL | NULL    |       |
| project_id | int(11) | NO   | MUL | NULL    |       |
+------------+---------+------+-----+---------+-------+

show tables;
+-------------------+
| Tables_in_company |
+-------------------+
| cats              |
| persons           |
| projects          |
| ptable            |
+-------------------+

Filling the Tables with Information

insert into cats (name) values ('Java'), ('.Net'), ('PHP');
insert into projects (name, cat_id) values ('Spring WebMVC webapp', 1), ('Swing Desctop App', 1), ('Silverlight App', 2), ('Linq Example', 2);
insert into persons (name) values ('Anton'), ('Andrew'), ('Nikolas');
insert into ptable values (1,1), (1,2), (2,2), (3,3);

select * from cats;
+----+------+
| id | name |
+----+------+
|  1 | Java |
|  2 | .Net |
|  3 | PHP  |
+----+------+

select * from projects;
+----+----------------------+--------+
| id | name                 | cat_id |
+----+----------------------+--------+
|  1 | Spring WebMVC webapp |      1 |
|  2 | Swing Desctop App    |      1 |
|  3 | Silverlight App      |      2 |
|  4 | Linq Example         |      2 |
+----+----------------------+--------+

select * from persons;
+----+---------+
| id | name    |
+----+---------+
|  1 | Anton   |
|  2 | Andrew  |
|  3 | Nikolas |
+----+---------+

select * from ptable;
+-----------+------------+
| person_id | project_id |
+-----------+------------+
|         1 |          1 |
|         1 |          2 |
|         2 |          2 |
|         3 |          3 |
+-----------+------------+

Making Multiple Select Requests (One-To-Many Relationship)

select * from projects;
+----+----------------------+--------+
| id | name                 | cat_id |
+----+----------------------+--------+
|  1 | Spring WebMVC webapp |      1 |
|  2 | Swing Desctop App    |      1 |
|  3 | Silverlight App      |      2 |
|  4 | Linq Example         |      2 |
+----+----------------------+--------+

select * from projects join cats;
+----+----------------------+--------+----+------+
| id | name                 | cat_id | id | name |
+----+----------------------+--------+----+------+
|  1 | Spring WebMVC webapp |      1 |  1 | Java |
|  1 | Spring WebMVC webapp |      1 |  2 | .Net |
|  1 | Spring WebMVC webapp |      1 |  3 | PHP  |
|  2 | Swing Desctop App    |      1 |  1 | Java |
|  2 | Swing Desctop App    |      1 |  2 | .Net |
|  2 | Swing Desctop App    |      1 |  3 | PHP  |
|  3 | Silverlight App      |      2 |  1 | Java |
|  3 | Silverlight App      |      2 |  2 | .Net |
|  3 | Silverlight App      |      2 |  3 | PHP  |
|  4 | Linq Example         |      2 |  1 | Java |
|  4 | Linq Example         |      2 |  2 | .Net |
|  4 | Linq Example         |      2 |  3 | PHP  |
+----+----------------------+--------+----+------+

select * from projects inner join cats;
+----+----------------------+--------+----+------+
| id | name                 | cat_id | id | name |
+----+----------------------+--------+----+------+
|  1 | Spring WebMVC webapp |      1 |  1 | Java |
|  1 | Spring WebMVC webapp |      1 |  2 | .Net |
|  1 | Spring WebMVC webapp |      1 |  3 | PHP  |
|  2 | Swing Desctop App    |      1 |  1 | Java |
|  2 | Swing Desctop App    |      1 |  2 | .Net |
|  2 | Swing Desctop App    |      1 |  3 | PHP  |
|  3 | Silverlight App      |      2 |  1 | Java |
|  3 | Silverlight App      |      2 |  2 | .Net |
|  3 | Silverlight App      |      2 |  3 | PHP  |
|  4 | Linq Example         |      2 |  1 | Java |
|  4 | Linq Example         |      2 |  2 | .Net |
|  4 | Linq Example         |      2 |  3 | PHP  |
+----+----------------------+--------+----+------+

select * from projects join cats on projects.cat_id=cats.id;
+----+----------------------+--------+----+------+
| id | name                 | cat_id | id | name |
+----+----------------------+--------+----+------+
|  1 | Spring WebMVC webapp |      1 |  1 | Java |
|  2 | Swing Desctop App    |      1 |  1 | Java |
|  3 | Silverlight App      |      2 |  2 | .Net |
|  4 | Linq Example         |      2 |  2 | .Net |
+----+----------------------+--------+----+------+

select * from projects as t1 join cats as t2 on t1.cat_id=t2.id;
+----+----------------------+--------+----+------+
| id | name                 | cat_id | id | name |
+----+----------------------+--------+----+------+
|  1 | Spring WebMVC webapp |      1 |  1 | Java |
|  2 | Swing Desctop App    |      1 |  1 | Java |
|  3 | Silverlight App      |      2 |  2 | .Net |
|  4 | Linq Example         |      2 |  2 | .Net |
+----+----------------------+--------+----+------+

select t1.id, t1.name, t2.name from projects as t1 join cats as t2 on t1.cat_id=t2.id;
+----+----------------------+------+
| id | name                 | name |
+----+----------------------+------+
|  1 | Spring WebMVC webapp | Java |
|  2 | Swing Desctop App    | Java |
|  3 | Silverlight App      | .Net |
|  4 | Linq Example         | .Net |
+----+----------------------+------+

select t1.id, t1.name, t2.name as project_name from projects as t1 join cats as t2 on t1.cat_id=t2.id;
+----+----------------------+--------------+
| id | name                 | project_name |
+----+----------------------+--------------+
|  1 | Spring WebMVC webapp | Java         |
|  2 | Swing Desctop App    | Java         |
|  3 | Silverlight App      | .Net         |
|  4 | Linq Example         | .Net         |
+----+----------------------+--------------+

select t1.id, t1.name, t2.name as project_name from projects as t1 right join cats as t2 on t1.cat_id=t2.id;
+------+----------------------+--------------+
| id   | name                 | project_name |
+------+----------------------+--------------+
|    1 | Spring WebMVC webapp | Java         |
|    2 | Swing Desctop App    | Java         |
|    3 | Silverlight App      | .Net         |
|    4 | Linq Example         | .Net         |
| NULL | NULL                 | PHP          |
+------+----------------------+--------------+

select t1.id, t1.name, t2.name as project_name from projects as t1 join cats as t2 on t1.cat_id=t2.id where t2.name='Java';
+------+----------------------+--------------+
| id   | name                 | project_name |
+------+----------------------+--------------+
|    1 | Spring WebMVC webapp | Java         |
|    2 | Swing Desctop App    | Java         |
+------+----------------------+--------------+

select t1.id, t1.name, t2.name as project_name, count(t1.id) from projects as t1 join cats as t2 on t1.cat_id=t2.id where t2.name='Java';
+------+----------------------+--------------+--------------+
| id   | name                 | project_name | count(t1.id) |
+------+----------------------+--------------+--------------+
|    1 | Spring WebMVC webapp | Java         |            2 |
+------+----------------------+--------------+--------------+

Making Multiple Select Requests (Many-To-Many Relationship)

select * from ptable join projects;
+-----------+------------+----+----------------------+--------+
| person_id | project_id | id | name                 | cat_id |
+-----------+------------+----+----------------------+--------+
|         1 |          1 |  1 | Spring WebMVC webapp |      1 |
|         1 |          2 |  1 | Spring WebMVC webapp |      1 |
|         2 |          2 |  1 | Spring WebMVC webapp |      1 |
|         3 |          3 |  1 | Spring WebMVC webapp |      1 |
|         1 |          1 |  2 | Swing Desctop App    |      1 |
|         1 |          2 |  2 | Swing Desctop App    |      1 |
|         2 |          2 |  2 | Swing Desctop App    |      1 |
|         3 |          3 |  2 | Swing Desctop App    |      1 |
|         1 |          1 |  3 | Silverlight App      |      2 |
|         1 |          2 |  3 | Silverlight App      |      2 |
|         2 |          2 |  3 | Silverlight App      |      2 |
|         3 |          3 |  3 | Silverlight App      |      2 |
|         1 |          1 |  4 | Linq Example         |      2 |
|         1 |          2 |  4 | Linq Example         |      2 |
|         2 |          2 |  4 | Linq Example         |      2 |
|         3 |          3 |  4 | Linq Example         |      2 |
+-----------+------------+----+----------------------+--------+

select * from ptable as x join projects as b on x.project_id = b.id;
+-----------+------------+----+----------------------+--------+
| person_id | project_id | id | name                 | cat_id |
+-----------+------------+----+----------------------+--------+
|         1 |          1 |  1 | Spring WebMVC webapp |      1 |
|         1 |          2 |  2 | Swing Desctop App    |      1 |
|         2 |          2 |  2 | Swing Desctop App    |      1 |
|         3 |          3 |  3 | Silverlight App      |      2 |
+-----------+------------+----+----------------------+--------+

select * from persons as a join (select * from ptable as x join projects as b on x.project_id = b.id) as xb;
+----+---------+-----------+------------+----+----------------------+--------+
| id | name    | person_id | project_id | id | name                 | cat_id |
+----+---------+-----------+------------+----+----------------------+--------+
|  1 | Anton   |         1 |          1 |  1 | Spring WebMVC webapp |      1 |
|  2 | Andrew  |         1 |          1 |  1 | Spring WebMVC webapp |      1 |
|  3 | Nikolas |         1 |          1 |  1 | Spring WebMVC webapp |      1 |
|  1 | Anton   |         1 |          2 |  2 | Swing Desctop App    |      1 |
|  2 | Andrew  |         1 |          2 |  2 | Swing Desctop App    |      1 |
|  3 | Nikolas |         1 |          2 |  2 | Swing Desctop App    |      1 |
|  1 | Anton   |         2 |          2 |  2 | Swing Desctop App    |      1 |
|  2 | Andrew  |         2 |          2 |  2 | Swing Desctop App    |      1 |
|  3 | Nikolas |         2 |          2 |  2 | Swing Desctop App    |      1 |
|  1 | Anton   |         3 |          3 |  3 | Silverlight App      |      2 |
|  2 | Andrew  |         3 |          3 |  3 | Silverlight App      |      2 |
|  3 | Nikolas |         3 |          3 |  3 | Silverlight App      |      2 |
+----+---------+-----------+------------+----+----------------------+--------+

select * from persons as a join (select * from ptable as x join projects as b on x.project_id = b.id) as xb on a.id=xb.person_id;
+----+---------+-----------+------------+----+----------------------+--------+
| id | name    | person_id | project_id | id | name                 | cat_id |
+----+---------+-----------+------------+----+----------------------+--------+
|  1 | Anton   |         1 |          1 |  1 | Spring WebMVC webapp |      1 |
|  1 | Anton   |         1 |          2 |  2 | Swing Desctop App    |      1 |
|  2 | Andrew  |         2 |          2 |  2 | Swing Desctop App    |      1 |
|  3 | Nikolas |         3 |          3 |  3 | Silverlight App      |      2 |
+----+---------+-----------+------------+----+----------------------+--------+

Trying to Invoke a Constraint

insert into projects (name, cat_id) values ('Ruby Web App', 6);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`company`.`projects`, CONSTRAINT `projects_ibfk_1` FOREIGN KEY (`cat_id`) REFERENCES `cats` (`id`))

delete from cats where name='Java';
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`company`.`projects`, CONSTRAINT `projects_ibfk_1` FOREIGN KEY (`cat_id`) REFERENCES `cats` (`id`))

To delete the database use 'drop database company;' query.

Thanks for the attention!