Many-to-Many Bridge Table in SQL Server: Model and Query It

A many-to-many bridge table solves a problem one table can’t: orders hold many plants, and plants sit on many orders. The bridge holds one row for every pair, and the two original tables stay clean.

Two One-to-Many Relationships

A customer can put many plants in one order. That’s a one-to-many relationship, but it’s only half the picture. A plant can also appear on many orders, so the relationship runs the other way too.

Together they make a many-to-many relationship. Books and authors follow the same pattern: a book can have several authors, and an author can write several books. A table can’t hold either side without repeating data, so a third table sits in the middle. People call it a bridge, junction or mapping table.

The Sample Tables

Orders and Plants hold the two sides. OrderItems is the bridge. Its primary key is the pair of columns (OrderID, PlantID), and each column is a foreign key to one parent. Qty belongs to the pair, since it says how many plants of one kind are on one order. Run this in any test database. Every result in this post comes from SQL Server 2025.

CREATE TABLE Plants (PlantID INT PRIMARY KEY, PlantName VARCHAR(20) NOT NULL, Price DECIMAL(6,2) NOT NULL);
CREATE TABLE Orders (OrderID INT PRIMARY KEY, Customer VARCHAR(20) NOT NULL, OrderDate DATE NOT NULL);
CREATE TABLE OrderItems (
  OrderID INT NOT NULL REFERENCES Orders (OrderID),
  PlantID INT NOT NULL REFERENCES Plants (PlantID),
  Qty INT NOT NULL,
  PRIMARY KEY (OrderID, PlantID));
INSERT INTO Plants (PlantID, PlantName, Price) VALUES (1, 'Fern', 12.50), (2, 'Cactus', 8.00), (3, 'Orchid', 29.00), (4, 'Basil', 4.50), (5, 'Bamboo', 15.00);
INSERT INTO Orders (OrderID, Customer, OrderDate) VALUES (101, 'Avery', '2026-03-02'), (102, 'Casey', '2026-03-09'), (103, 'Avery', '2026-04-11'), (104, 'Jordan', '2026-04-18');
INSERT INTO OrderItems (OrderID, PlantID, Qty) VALUES (101, 1, 2), (101, 4, 3), (102, 1, 1), (102, 3, 1), (103, 4, 2), (103, 2, 4), (104, 4, 1);
GO

SELECT OrderID, Customer, OrderDate FROM Orders ORDER BY OrderID;

SELECT OrderID, PlantID, Qty FROM OrderItems ORDER BY OrderID, PlantID;

SELECT PlantID, PlantName, Price FROM Plants ORDER BY PlantID;

Two Questions, Two Directions

The bridge answers questions from either side. The first asks which plants are on order 101. The second asks on how many orders Basil appears.

SELECT p.PlantName, i.Qty
FROM OrderItems AS i
INNER JOIN Plants AS p ON p.PlantID = i.PlantID
WHERE i.OrderID = 101
ORDER BY p.PlantName;

Order 101 holds 3 Basil and 2 Fern. Now the other direction.

SELECT COUNT(*) AS OrdersWithBasil
FROM OrderItems AS i
INNER JOIN Plants AS p ON p.PlantID = i.PlantID
WHERE p.PlantName = 'Basil';

Basil appears on 3 orders. Both questions use the same bridge table, and only the filter changes.

The Three-Table Join

To see order details with plant names and prices, join all three tables. The bridge comes first, and each parent joins to it by its key.

SELECT o.OrderID, o.Customer, p.PlantName, i.Qty, i.Qty * p.Price AS LineTotal
FROM OrderItems AS i
INNER JOIN Orders AS o ON o.OrderID = i.OrderID
INNER JOIN Plants AS p ON p.PlantID = i.PlantID
ORDER BY o.OrderID, p.PlantName;

That returns seven rows, one for each row in OrderItems. The line total is the quantity times the price from Plants.

Filtering by a Parent Column

A customer’s name lives in Orders, not in the bridge. To filter on it, Orders must be part of the join.

SELECT i.OrderID, p.PlantName, i.Qty
FROM OrderItems AS i
INNER JOIN Orders AS o ON o.OrderID = i.OrderID
INNER JOIN Plants AS p ON p.PlantID = i.PlantID
WHERE o.Customer = 'Avery'
ORDER BY i.OrderID, p.PlantName;

That returns the four rows for Avery’s two orders. Leave Orders out of the join, and the filter has nothing to refer to.

SELECT i.OrderID, p.PlantName, i.Qty
FROM OrderItems AS i
INNER JOIN Plants AS p ON p.PlantID = i.PlantID
WHERE o.Customer = 'Avery';

SQL Server answers with Msg 4104: the multi-part identifier o.Customer could not be bound. The alias o was never defined, so the query never ran.

The Keys Protect the Bridge

The composite primary key allows each pair once. A second row for order 101 and Fern is refused.

INSERT INTO OrderItems (OrderID, PlantID, Qty) VALUES (101, 1, 5);

That returns Msg 2627, a duplicate key. To change a quantity, update the existing row. The foreign keys protect the other side: a plant that doesn’t exist can’t go on an order.

INSERT INTO OrderItems (OrderID, PlantID, Qty) VALUES (101, 99, 1);

That returns Msg 547, a foreign key conflict. Both checks happen in the database, so no application has to remember them.

Plants Nobody Ordered

An inner join drops any plant without a bridge row. This query counts the orders for each plant, and Bamboo never appears.

SELECT p.PlantName, COUNT(*) AS OrdersWithIt
FROM Plants AS p
INNER JOIN OrderItems AS i ON i.PlantID = p.PlantID
GROUP BY p.PlantName
ORDER BY OrdersWithIt DESC, p.PlantName;

Four plants come back. Switch to a left outer join, and the plants with no orders show up with a count of zero.

SELECT p.PlantName, COUNT(i.OrderID) AS OrdersWithIt
FROM Plants AS p
LEFT OUTER JOIN OrderItems AS i ON i.PlantID = p.PlantID
GROUP BY p.PlantName
ORDER BY OrdersWithIt DESC, p.PlantName;

Now Bamboo is listed with 0 orders. Use the inner join when you want only plants that sold, and the left join when you want every plant.

Clean Up

Remove the sample tables when you finish. The bridge goes first, because it points to the other two.

DROP TABLE OrderItems;
DROP TABLE Orders;
DROP TABLE Plants;

What to Remember

Model a many-to-many relationship with a bridge table. Give it a composite primary key and one foreign key for each side. Join all three tables whenever a filter or a column comes from a parent.

To go further, read Tagging With a Many-to-Many Table: Storing and Querying Hashtags, Using Bitwise And Instead of a Junction Table and Finding Orphaned Child Rows Before Adding a Foreign Key.

A bridge table is not extra work, it is the only honest way to store a many-to-many fact.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.