One-to-many vs many-to-one relationships

A one-to-many relationship means one row in a table can connect to multiple rows in another table.

About 5–10 minutes · no sign-up needed

Example

Understanding One-to-Many Retail Relationships

SELECT customer_id, COUNT(order_id) AS order_count
FROM orders
GROUP BY customer_id
ORDER BY order_count DESC

Result:

After SELECT, list the column names you want, separated by commas.

SELECT c.customer_id, c.region, o.order_id, o.revenue, o.order_date

FROM tells SQL which table to pull the data from.

SELECT c.customer_id, c.region, o.order_id, o.revenue, o.order_date
FROM customers c

JOIN combines rows from two tables that share a key.

SELECT c.customer_id, c.region, o.order_id, o.revenue, o.order_date
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id

Try each one in your head first, then open the answer.

1. Join the orders table to the customers table on customer_id and return customer_id, orders.order_id, and customers.full_name.

SELECT a.customer_id, a.order_id, b.full_name
FROM orders a
JOIN customers b ON a.customer_id = b.customer_id

2. Join the orders table to the shipments table on order_id and return order_id, orders.customer_id, and shipments.shipment_id.

SELECT a.order_id, a.customer_id, b.shipment_id
FROM orders a
JOIN shipments b ON a.order_id = b.order_id

3. Join the orders table to the returns table on order_id and return order_id, orders.customer_id, and returns.return_id.

SELECT a.order_id, a.customer_id, b.return_id
FROM orders a
JOIN returns b ON a.order_id = b.order_id

4. Join the products table to the reviews table on product_id and return product_id, products.brand, and reviews.review_id.

SELECT a.product_id, a.brand, b.review_id
FROM products a
JOIN reviews b ON a.product_id = b.product_id

5. Join the products table to the inventory table on product_id and return product_id, products.brand, and inventory.warehouse.

SELECT a.product_id, a.brand, b.warehouse
FROM products a
JOIN inventory b ON a.product_id = b.product_id

Learn it by doing. This lesson is a short hands-on level on Bampaa: see it work, then build and write it yourself, with instant feedback.

About 5–10 minutes · no sign-up needed