I have these tables:
CREATE TABLE customer_orders (
"order_id" INT,
"customer_id" INTEGER,
"pizza_id" INTEGER,
"exclusions" VARCHAR(4),
"extras" VARCHAR(4),
"order_time" DATETIME
);
INSERT INTO customer_orders
("order_id", "customer_id", "pizza_id", "exclusions", "extras", "order_time")
VALUES
('1', '101', '1', '', '', '2020-01-01 18:05:02'),
('2', '101', '1', '', '', '2020-01-01 19:00:52'),
('3', '102', '1', '', '', '2020-01-02 23:51:23'),
('3', '102', '2', '', NULL, '2020-01-02 23:51:23'),
('4', '103', '1', '4', '', '2020-01-04 13:23:46'),
('4', '103', '1', '4', '', '2020-01-04 13:23:46'),
('4', '103', '2', '4', '', '2020-01-04 13:23:46'),
('5', '104', '1', NULL, '1', '2020-01-08 21:00:29'),
('6', '101', '2', NULL, NULL, '2020-01-08 21:03:13'),
('7', '105', '2', NULL, '1', '2020-01-08 21:20:29'),
('8', '102', '1', NULL, NULL, '2020-01-09 23:54:33'),
('9', '103', '1', '4', '1, 5', '2020-01-10 11:22:59'),
('10', '104', '1', NULL, NULL, '2020-01-11 18:34:49'),
('10', '104', '1', '2, 6', '1, 4', '2020-01-11 18:34:49');
CREATE TABLE pizza_toppings (
"topping_id" INTEGER,
"topping_name" VARCHAR(50)
);
INSERT INTO pizza_toppings
("topping_id", "topping_name")
VALUES
(1, 'Bacon'),
(2, 'BBQ Sauce'),
(3, 'Beef'),
(4, 'Cheese'),
(5, 'Chicken'),
(6, 'Mushrooms'),
(7, 'Onions'),
(8, 'Pepperoni'),
(9, 'Peppers'),
(10, 'Salami'),
(11, 'Tomatoes'),
(12, 'Tomato Sauce')
CREATE TABLE pizza_names (
"pizza_id" INTEGER,
"pizza_name" VARCHAR
);
INSERT INTO pizza_names
("pizza_id", "pizza_name")
VALUES
(1, 'Meatlovers'),
(2, 'Vegetarian');
CREATE TABLE pizza_recipes (
"pizza_id" INTEGER,
"toppings" VARCHAR
);
INSERT INTO pizza_recipes
("pizza_id", "toppings")
VALUES
(1, '1, 2, 3, 4, 5, 6, 8, 10'),
(2, '4, 6, 7, 9, 11, 12');
And I’m asked: Generate an alphabetically ordered comma separated ingredient list for each pizza order from the customer_orders table.
- For example: "Meat Lovers: Bacon, Beef, … , Salami"
So far I did this queries that separed work but when Im building it as a subquery is not working:
SELECT CONCAT(pizza_name + ':',
(SELECT toppings_per_recipe
FROM
(SELECT string_agg(topping_name, ', ') as toppings_per_recipe
FROM pizza_recipes as pr
cross apply string_split(pr.toppings, ',')
JOIN pizza_toppings AS pt
ON topping_id = VALUE GROUP BY pr.pizza_id)as o))
FROM customer_orders AS co
JOIN pizza_names AS pn
ON co.pizza_id = pn.pizza_id
when I run this it says ‘Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.’
but I don’t know how to fix it and get the results I need (Im new in SQL so I’m a little lost here).
>Solution :
I think this is what you intended. When you write it to use a lookup then you’ll need to restrict (correlate) to a single row. There was also another level of nesting that served no purpose:
SELECT CONCAT(pizza_name + ':',
(
SELECT string_agg(topping_name, ', ') within group (order by topping_name)
FROM pizza_recipes as pr cross apply string_split(pr.toppings, ',')
JOIN pizza_toppings AS pt ON topping_id = VALUE
WHERE pr.pizza_id = pn.pizza_id
)
) as toppings_per_recipe
FROM customer_orders AS co JOIN pizza_names AS pn
ON co.pizza_id = pn.pizza_id;
To avoid the correlation you could use a derived table that generates the long names:
SELECT CONCAT(pizza_name, ':', toppings_per_recipe)
FROM customer_orders AS co JOIN pizza_names AS pn
ON co.pizza_id = pn.pizza_id
JOIN (
SELECT pr.pizza_id,
string_agg(topping_name, ', ') within group (order by topping_name) as toppings_per_recipe
FROM pizza_recipes as pr cross apply string_split(pr.toppings, ',')
JOIN pizza_toppings AS pt ON topping_id = VALUE
GROUP BY pr.pizza_id
) as pizza_desc ON pizza_desc.pizza_id = pn.pizza_id;