Select all orders that have the exact same items as this order with MySQL

Orders.sql

	DROP TABLE Items;
	DROP TABLE Orders;
	DROP TABLE OrdersItems;

	CREATE TABLE Items (
		ItemId int NOT NULL,
		ItemName nvarchar(50) NULL
	);
	
	CREATE TABLE Orders (
		OrderId int NOT NULL,
		OrderName nvarchar(50) NULL,
		BoxType nvarchar(50) NULL
	);

	CREATE TABLE OrdersItems(
		OrderId int NOT NULL,
		ItemId int NOT NULL,
		Quantity int  NOT NULL
	);

	INSERT INTO Items (ItemId, ItemName) VALUES (1, 'Apples'),(2, 'Oranges'),(3, 'Pears');

	-- order 1 and 3 are the same
	INSERT INTO Orders (OrderId, OrderName, BoxType) VALUES 
		(1, 'Order1', 'small'),
		(2, 'Order2', 'medium'),
		(3, 'Order3', 'small'),
		(4, 'Order4', 'large'),
		(5, 'Order5', 'small'),
		(6, 'Order6', 'small'),
		(7, 'Order7', 'large');

	INSERT INTO OrdersItems (OrderId, ItemId, Quantity) VALUES (1, 1, 10),(1, 2, 5);
	INSERT INTO OrdersItems (OrderId, ItemId, Quantity) VALUES (2, 1, 5),(2, 2, 7),(2, 3, 2);
	INSERT INTO OrdersItems (OrderId, ItemId, Quantity) VALUES (3, 1, 10),(3, 2, 5);
	INSERT INTO OrdersItems (OrderId, ItemId, Quantity) VALUES (4, 1, 10),(4, 2, 5),(4, 3, 2);
	INSERT INTO OrdersItems (OrderId, ItemId, Quantity) VALUES (5, 1, 4),(5, 3, 1);
	INSERT INTO OrdersItems (OrderId, ItemId, Quantity) VALUES (6, 1, 10),(6, 2, 5);
	INSERT INTO OrdersItems (OrderId, ItemId, Quantity) VALUES (7, 1, 10),(7, 2, 5);


-- All Orders
	SELECT O.OrderName, O.BoxType, I.ItemName, OI.Quantity
	FROM Orders O
	LEFT JOIN OrdersItems OI ON (O.OrderId=OI.OrderId)
	LEFT JOIN Items I ON (OI.ItemId=I.ItemId);


-- Order 1
	SELECT O.OrderName, O.BoxType, I.ItemName, OI.Quantity
	FROM Orders O
	LEFT JOIN OrdersItems OI ON (O.OrderId=OI.OrderId)
	LEFT JOIN Items I ON (OI.ItemId=I.ItemId)
	WHERE O.OrderId=1;

-- Orders with at least the items Order1 has
	SELECT DISTINCT O2.OrderName
	FROM Orders O1
	LEFT JOIN OrdersItems OI1 ON (O1.OrderId=OI1.OrderId)
	LEFT JOIN Items I1 ON (OI1.ItemId=I1.ItemId)
	LEFT JOIN OrdersItems OI2 ON (OI1.ItemId=OI2.ItemId AND OI1.OrderId<>OI2.OrderId AND OI1.Quantity=OI2.Quantity)
	LEFT JOIN Orders O2 ON (OI2.OrderId=O2.OrderId)
	WHERE O1.OrderId=1;


-- Orders with items that Order1 does NOT have
	SELECT DISTINCT O1.OrderName
	FROM Orders O1
	LEFT JOIN OrdersItems OI1 ON (O1.OrderId=OI1.OrderId)
	CROSS JOIN Orders O2
	LEFT JOIN OrdersItems OI2 ON (O2.OrderId=OI2.OrderId AND OI1.ItemId=OI2.ItemId and OI1.Quantity=OI2.Quantity)
	WHERE O1.OrderId<>1
	AND O2.OrderId=1
	AND OI2.ItemId IS NULL;

-- Final result
	SELECT DISTINCT O2.OrderName, O2.BoxType
	FROM Orders O1
	LEFT JOIN OrdersItems OI1 ON (O1.OrderId=OI1.OrderId)
	LEFT JOIN Items I1 ON (OI1.ItemId=I1.ItemId)
	LEFT JOIN OrdersItems OI2 ON (OI1.ItemId=OI2.ItemId AND OI1.OrderId<>OI2.OrderId AND OI1.Quantity=OI2.Quantity)
	LEFT JOIN Orders O2 ON (OI2.OrderId=O2.OrderId)
	WHERE O1.OrderId=1
	AND O2.OrderId NOT IN
	(
		SELECT DISTINCT O1.OrderId
		FROM Orders O1
		LEFT JOIN OrdersItems OI1 ON (O1.OrderId=OI1.OrderId)
		CROSS JOIN Orders O2
		LEFT JOIN OrdersItems OI2 ON (O2.OrderId=OI2.OrderId AND OI1.ItemId=OI2.ItemId and OI1.Quantity=OI2.Quantity)
		WHERE O1.OrderId<>1
		AND O2.OrderId=1
		AND OI2.ItemId IS NULL
	);
	
-- Just the box type
	SELECT DISTINCT O2.BoxType
	FROM Orders O1
	LEFT JOIN OrdersItems OI1 ON (O1.OrderId=OI1.OrderId)
	LEFT JOIN Items I1 ON (OI1.ItemId=I1.ItemId)
	LEFT JOIN OrdersItems OI2 ON (OI1.ItemId=OI2.ItemId AND OI1.OrderId<>OI2.OrderId AND OI1.Quantity=OI2.Quantity)
	LEFT JOIN Orders O2 ON (OI2.OrderId=O2.OrderId)
	WHERE O1.OrderId=1
	AND O2.OrderId NOT IN
	(
		SELECT DISTINCT O1.OrderId
		FROM Orders O1
		LEFT JOIN OrdersItems OI1 ON (O1.OrderId=OI1.OrderId)
		CROSS JOIN Orders O2
		LEFT JOIN OrdersItems OI2 ON (O2.OrderId=OI2.OrderId AND OI1.ItemId=OI2.ItemId and OI1.Quantity=OI2.Quantity)
		WHERE O1.OrderId<>1
		AND O2.OrderId=1
		AND OI2.ItemId IS NULL
	);
	
-- Sorted by most popular box type
	SELECT O2.BoxType, COUNT(DISTINCT O2.OrderId) Frequency
	FROM Orders O1
	LEFT JOIN OrdersItems OI1 ON (O1.OrderId=OI1.OrderId)
	LEFT JOIN Items I1 ON (OI1.ItemId=I1.ItemId)
	LEFT JOIN OrdersItems OI2 ON (OI1.ItemId=OI2.ItemId AND OI1.OrderId<>OI2.OrderId AND OI1.Quantity=OI2.Quantity)
	LEFT JOIN Orders O2 ON (OI2.OrderId=O2.OrderId)
	WHERE O1.OrderId=1
	AND O2.OrderId NOT IN
	(
		SELECT DISTINCT O1.OrderId
		FROM Orders O1
		LEFT JOIN OrdersItems OI1 ON (O1.OrderId=OI1.OrderId)
		CROSS JOIN Orders O2
		LEFT JOIN OrdersItems OI2 ON (O2.OrderId=OI2.OrderId AND OI1.ItemId=OI2.ItemId and OI1.Quantity=OI2.Quantity)
		WHERE O1.OrderId<>1
		AND O2.OrderId=1
		AND OI2.ItemId IS NULL
	)
	GROUP BY O2.BoxType
	ORDER BY COUNT(DISTINCT O2.OrderId) DESC;