1:N table. Select case depending on multiple different values in N table

I have two tables, Order and OrderDetail, with a 1:N relation. The OrderDetail table has a Category column.

When I select from the Order table, I want to select one column with a CASE statement. If all OrderDetail column's category is food, I want select it as "Food". But if there are many different categories in OrderDetail table, I want select it as "Mixed". There are many kinds of category.

So if an order only includes book/food/etc, I want it select as it is. Otherwise, mixed.

I only know basics of SQL. So I was looking for a way to accomplish this. I guess I need subquery but I could not write query at all.

select 
 case 
   when not exists (select 1
     from order_details d where m.order_id=d.order_id
     and d.category!='Food') then 'Food' --- there is no row having other cathegory than Food
   else 'Mixed'
  end
 ,m.*
from orders m;

Or, it could be:

with ord_categs (
  select order_id, category, count(distinct category) cnt_categ
    ,row_number() over (partition by order_id order by category) rn
  from order_details
  group by order_id, category
)
select 
  m.*
  ,case
    when coalesce(c.cnt_categ,1)=1 then c.category --- null if no details exist
    else 'Mixed'
  end
from orders m
left join ord_categs c on m.order_id=c.order_id and 1=rn;

You can use a correlated sub-query and count the number of different categories and if there is only one category then display that else display Mixed (assuming that you have a primary key column in the orders table, such as id, and a foreign key column in the order_details table, such as order_id, that references the previous primary key):

SELECT o.*,
       ( SELECT CASE COUNT(DISTINCT category)
                WHEN 1
                THEN MAX(category)
                ELSE 'Mixed'
                END
         FROM   order_details d
         WHERE  o.id = d.order_id ) AS category
FROM   orders o;

Which, for the sample data:

CREATE TABLE orders (id, name) AS
SELECT 1, 'order1' FROM DUAL UNION ALL
SELECT 2, 'order2' FROM DUAL UNION ALL
SELECT 3, 'order3' FROM DUAL UNION ALL
SELECT 4, 'order4' FROM DUAL;

CREATE TABLE order_details (id, order_id, category) AS
SELECT 1, 1, 'Food' FROM DUAL UNION ALL
SELECT 2, 2, 'Food' FROM DUAL UNION ALL
SELECT 3, 2, 'Book' FROM DUAL UNION ALL
SELECT 4, 3, 'Book' FROM DUAL UNION ALL
SELECT 5, 3, 'Book' FROM DUAL UNION ALL
SELECT 6, 4, 'Food' FROM DUAL UNION ALL
SELECT 7, 4, 'Book' FROM DUAL;

Outputs:

fiddle