# Many-to-Many

Why two tables can't link many-to-many directly, and how an associative (junction) table between them turns it into two one-to-many relationships.

Journeyman · SQL · Database · Updated November 15, 2026

By Gregor Dzierzon

A **many-to-many** relationship cannot exist as a direct link between two tables. This is the concept that many rows in one table can be related to many rows in another. Adding this type of a `PK` to `FK` relationship is not possible.

For instance, one order could contain many products. But a product could be ordered by many customers, or even by the same customer on multiple occasions. In other words, one product can be contained in many orders. This is a **many-to-many** relationship.

![Many-to-Many relationship between the order and product tables](https://fsdtrainingmedia.blob.core.windows.net/public/images/2026/09/d5d04168e22c436388206f37cf05f4f7.png "Many-to-Many relationship between the order and product tables")

This direct relationship presents a challenge though, that needs to be addressed. Which table should contain the details of the relationship?

If an order can have many products, then should the product table contain the `order_id` foreign key along with all related data, such as quantity? But then each product would be limited to existing for just one order.

If a product can be ordered many times, then should the order table contain the `product_id` foreign key as well as all associated data, such as quantity? With this design, each order could then only contain a single product. A customer would need to create a new order for each product they want to purchase.

## Two One-to-Many Relationships

The solution is a third table between them, called an associative table (or junction table). It holds one row for each order and product pair, with that pair's details, such as the quantity. The **many-to-many** relationship becomes two one-to-many relationships: an order has many line items, and a product appears in many line items.

![The line_items table is the associative table](https://fsdtrainingmedia.blob.core.windows.net/public/images/2026/09/bdfeb7a10bf44270927b08de33421dfb.png "The line_items table is the associative table")

## Do it: add a new order's line items

An order can hold many products, and a product can be in many orders. The `LineItems` table sits between `Orders` and `Products` and holds one row for each order-and-product pair, with how many of that product the order wants.

The three tables (the **Tables** tab shows the SQL that makes them):

`Orders`:

| OrderId | CompanyName |
|---|---|
| 1 | Alfreds Futterkiste |
| 2 | Bon app' |
| 3 | Around the Horn |

`Products`:

| ProductId | ProductName |
|---|---|
| 1 | Chai |
| 2 | Chang |
| 3 | Tofu |

`LineItems`, the associative table. Its `OrderId` points at `Orders`, and its `ProductId` at `Products`:

| OrderId | ProductId | Quantity |
|---|---|---|
| 1 | 1 | 10 |
| 1 | 3 | 2 |
| 2 | 1 | 5 |

So order 1 is two rows (10 Chai and 2 Tofu), and Chai is in two orders (1 and 2).

Order 3 has just come in from Around the Horn, and it has no line items yet. It's for **4 Chang and 6 Tofu**. Working it through, each product in the order becomes one row:

| In the order | OrderId | ProductId (from `Products`) | Quantity |
|---|---|---|---|
| 4 Chang | 3 | 2 | 4 |
| 6 Tofu | 3 | 3 | 6 |

After your SQL runs, `LineItems` should hold exactly these rows (shown by `OrderId`, then `ProductId`):

| OrderId | ProductId | Quantity |
|---|---|---|
| 1 | 1 | 10 |
| 1 | 3 | 2 |
| 2 | 1 | 5 |
| 3 | 2 | 4 |
| 3 | 3 | 6 |

**Your job:** add order 3's line items to `LineItems`.

- Add one row for each product in order 3, with its `OrderId`, `ProductId` and `Quantity`.
- Leave the rows of orders 1 and 2 as they are.

Practiced live on the lesson page: write the SQL right there and check it.

## Key terms

- [Many-to-Many Relationship](https://www.ask-codey.com/glossary/many-to-many-relationship)
- [Junction Table](https://www.ask-codey.com/glossary/junction-table)

## Cheat sheets

- [Many-to-Many](https://www.ask-codey.com/cheat-sheets/database-relationships-many-to-many-cheat-sheet)

Source: https://www.ask-codey.com/lessons/database-relationships-many-to-many
