SQL: Many-to-Many and One-to-Many

Here’s a short tutorial on how to solve some basic SQL cardinalities. First have a look at the ER-diagram for the database and the resulting tables in PhpMyAdmin.

Many to many and one to many
Many to many and one to many
The ER-Diagram transformed to tables in PhpMyAdmin
The ER-Diagram transformed to tables in PhpMyAdmin

Many-to-Many

Many to many cardinalities may be solved like this:


SELECT cake, who
FROM cakes, costumers, likes
WHERE costumers.costumers_id = likes.costumers_id
AND cakes.cakes_id = likes.cakes_id

Note that there are two where clauses combined via AND.

One-to-Many

This cardinality is more easy:


SELECT inventory .* , costumers.who
FROM inventory, costumers
WHERE inventory.costumers_id = costumers.costumers_id

In the “many” table you simply connect via the “one” table’s id.

You can download and try out the samples from my Github Repository oneToMany_ManyToMany.

Read On

These samples are inspired by Ben Forta’s “MySQL Crash Course” Ch. 15 – 16.

Published by

Per Thykjaer Jensen

I am a Senior Lecturer in front- and backend web programming at the Multimedia Design and Communication Programme at Business Academy Aaarhus. I speak several languages: PHP, Python, MySQL, JavaScript, Nodejs, HTML, CSS, and ... Danish. Background: MA it, interaction and multimedia - MA Literature history - BA Art History.

Leave a Reply