CASE query to get results from different tables

Question

I am working on restaurant project. The restaurant takes online orders and "in restaurant" orders

I need to print out all active orders with 1 query. The problem comes when I need to associate online order with specific customer.. For "in restaurant" orders I don't need to associate the order with customer_id but I need to associate table_id with its name.. My database looks like:

Customers

id	customer_names
10	John
11	Peter

Tables

id	table_name
1	Table 1
2	Table 2

Orders

id	table_id	customer_id
1	0	10
2	1	0
3	2	0

My query is:

SELECT t.id, o.id as oid, (
    CASE WHEN t.id > 0 THEN 
        t.table_name
    ELSE 
        c.customer_names
    END
) AS table_name 
FROM tables t, orders o, customers c
WHERE o.table_id = t.id
GROUP BY o.id order by o.id asc;

What I am trying to achieve is when o.table_id > 0 I need to see t.table_name otherwise the order is for delivery and than I should see customer_names instead of table_name

How can I do so? Thanks for your time!

Answer 1

See example

select id
  ,table_id
  ,case when table_id>0 then 
     (select min(table_name) table_name from tables t where o.table_id=t.id) 
   end table_name
  ,customer_id
  ,case when customer_id>0 then 
     (select min(customer_names) customer_name from customers c 
      where o.customer_id=c.id) 
   end customer_name
from orders o

id	table_id	table_name	customer_id	customer_name
1	0	null	10	John
2	1	Table 1	0	null
3	2	Table 2	0	null