Generate Product Variants for E-commerce

basic

An online clothing store needs to create all possible product variants by combining products with available sizes. Each product should be paired with every size to create individual SKUs.

-- Products table

CREATE TABLE products (

id INT PRIMARY KEY,

name VARCHAR(100),

base_price DECIMAL(10,2)

);

INSERT INTO products VALUES

(1, 'T-Shirt', 19.99),

(2, 'Jeans', 49.99),

(3, 'Hat', 14.99);

-- Sizes table

CREATE TABLE sizes (

id INT PRIMARY KEY,

name VARCHAR(20),

price_modifier DECIMAL(10,2)

);

INSERT INTO sizes VALUES

(1, 'Small', 0.00),

(2, 'Medium', 0.00),

(3, 'Large', 2.00);

-- Generate all product-size combinations

SELECT

p.id AS product_id,

p.name AS product,

s.name AS size,

p.base_price + s.price_modifier AS final_price,

CONCAT(p.name, ' - ', s.name) AS sku_name

FROM products p

CROSS JOIN sizes s

ORDER BY p.name, s.name;

products: 3 rows (T-Shirt, Jeans, Hat)

sizes: 3 rows (Small, Medium, Large)

product_id | product | size | final_price | sku_name

1 | T-Shirt | Small | 19.99 | T-Shirt - Small

1 | T-Shirt | Medium | 19.99 | T-Shirt - Medium

1 | T-Shirt | Large | 21.99 | T-Shirt - Large

2 | Jeans | Small | 49.99 | Jeans - Small