Comparing values in one column to get a calculated value if values are different-CodePudding

I am trying to print a column with calculated values from two tables in SQL. I need to compare both the order_items and orders tablea. I have the below two tables:

Order_Items: enter image description here

Orders: enter image description here

Expected outcome. If there are different brands for an order_no in order_items table, it should show "multiple" in Brand column, if there are same brands the it should show the brand name:

enter image description here

CodePudding user response：

We can handle this via a single pass over the Order_Items table:

SELECT *, CASE WHEN MIN(brand) OVER (PARTITION BY order_no) =
                    MAX(brand) OVER (PARTITION BY order_no)
               THEN brand ELSE 'multiple' END AS label
FROM Order_Items
ORDER BY order_no;

CodePudding user response：

You can use simple aggregation. In this case a correlated subquery with the aggregation might be easier

SELECT
  o.*,
  oi.brand
FROM Orders o
CROSS APPLY (
    SELECT brand = CASE WHEN MIN(brand) = MAX(brand)
               THEN MIN(brand) ELSE 'multiple' END
    FROM Order_Items oi
    WHERE oi.order_no = o.order _no
) oi;