why is this subquery not working? SQL Server begginer practice case

qxsslcnc 于 2023-06-21 发布在 SQL Server

关注(0)|答案(2)|浏览(142)

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).

sql-server

来源：https://stackoverflow.com/questions/76460652/why-is-this-subquery-not-working-sql-server-begginer-practice-case

2条答案

按热度按时间

insrf1ej1#

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;

https://dbfiddle.uk/ruGdPJ2c

赞(0）回复(0）举报 2023-06-21

gr8qqesn2#

you can add the name drie4ctly to the ingridients

SELECT CONCAT(pn.pizza_name,': ', 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 
JOIN pizza_names AS pn ON pr."pizza_id" =  pn.pizza_id
GROUP BY pn.pizza_id,pn.pizza_name

toppings_per_recipe
Meatlovers: Bacon, BBQ Sauce, Beef, Cheese, Chicken, Mushrooms, Pepperoni, Salami
Vegetarian: Cheese, Mushrooms, Onions, Peppers, Tomatoes, Tomato Sauce

fiddle

赞(0）回复(0）举报 2023-06-21

我来回答

why is this subquery not working? SQL Server begginer practice case

2条答案

相关问题

热门标签

最新问答