Joining Tables In SQL with no success

Question

i have three tables in sql and i want to join them.

one of the tables are the menu (tab007-with 4 columns primary key, name of the menu, expenses and price) of a restaurant and the other one is the customers table (bestel) and the last one is the orders (post with four columns, primary key, primary key of first table, primary key of second table and number of orders) made this week.

i want to display which menu is the favorite one and which menu has the highest benefit for the owner of the restaurant.

i have tried a couple of select statements but with no success. i'll be happy to get help from here. here is my select statement:

(SELECT T.MID, T.NAME         
FROM TAB007 AS T              
INNER JOIN POST AS T1         
ON T.MID=T1.MID)              
SELECT SUM(ANZ) FROM T1.POST  
GROUP BY MID;

You say you want to join 3 tables but there are only two in your code example? — Joakim Danielson, Jun 04 '18 at 06:47

Barbaros Özhan · Answer 1 · 2018-06-04T06:44:01.867

1

You may just use this :

SELECT T.MID, T.NAME, SUM(T1.ANZ) as SUM_ANZ
  FROM TAB007 T
    INNER JOIN POST T1 ON (T.MID=T1.MID)
 GROUP BY T.MID, T.NAME
 ORDER BY SUM_ANZ DESC;

edited Jun 04 '18 at 06:44

answered Jun 04 '18 at 06:00

Barbaros Özhan

59,113
10
31
55

`ORDER BY ` has been deprecated for decades. Use a column alias instead. – jarlh Jun 04 '18 at 06:41
@BarbarosÖzhan i have one more question.how could i change this query to display only the first raw? – Sara M Jun 04 '18 at 09:17
@SaraM I don't know how it can be done in DB2, as an example in Oracle `rownum=1` might be used. – Barbaros Özhan Jun 04 '18 at 11:31

Tatyana · Answer 2 · 2018-06-04T07:49:22.843

To find the favourite menu, you can use this:

select t.tname,p.tid,sum(p.orders) from post p, tab007 t 
where t.tid=p.tid 
group by p.tid 
order by sum desc;

To find menu with the highest benefit, the following can be kept in mind:

**** As I did not understand the fields of expense and price from your question, I considered expense to be less than the quoted price of the item and the benefit will be the difference between expense and price. So higher the difference on all orders of the particular item, higher is the benefit. ****

Based on my above statement, I have the following code:

select t.tname, sum((t.tprice-t.texpense)*p.orders) from post p, tab007 t 
where p.tid=t.tid 
group by p.tid 
order by sum desc;

Joakim Danielson · Accepted Answer · 2018-06-04T07:51:50.270

0

Assuming only two tables are needed you can calculate popularity and profit like this

SELECT T.mid, T.name, SUM(p.anz) favorites, SUM((T.price - t.expense) * p.anz) benefit
FROM TAB007 T
JOIN post P on p.mid = t.mid
GROUP BY t.mid, t.name
ORDER BY favorites DESC

To also include menus that has no orders at all in the result you can use left join and case ... is null

SELECT T.mid, T.name, 
CASE WHEN p.anz IS NULL 
  THEN 0
  ELSE SUM(p.anz) END AS favorites, 
CASE WHEN p.anz IS NULL 
  THEN 0
  ELSE SUM((T.price-t.expense)*p.anz) END AS benefit
FROM menu T
LEFT JOIN post P on p.mid = t.mid
GROUP BY t.mid, t.name
ORDER BY favorites DESC

edited Jun 04 '18 at 07:51

answered Jun 04 '18 at 07:45

Joakim Danielson

43,251
5
22
52

could you possibly give me an idea with the rest of the select query? which is Extend the query so that the sum of the cost of all sold menus, the sum of the resulting income and the profit isalso displayed – Sara M Jun 04 '18 at 09:00
actually it would be enough to know : how could i change this query to show only one raw?i.e. i need only the one which has the highest profit and/or the one which is favorit – Sara M Jun 04 '18 at 09:49
@SaraM That depends on what DBMS you are using, for MySQL for instance you write `LIMIT 1` after `ORDER BY...` to only get the first row. – Joakim Danielson Jun 04 '18 at 10:14
@SaraM, [look here](https://stackoverflow.com/questions/3885193/equivalent-of-limit-for-db2) – Joakim Danielson Jun 04 '18 at 10:54

score 0 · Answer 4 · answered Jun 04 '18 at 08:02

menu table (TAB007) columns - (mid, name, expanse, price)
customers table (bestel) column - (cid, name)
order table (post) columns - (pid, mid, cid, number_of_order)

Favorite menu : I guess with highest number of total order and
Highest benefit menu : which has more benefit after total_number_of_order*(price - expense).

use LEFT JOIN to display all menu items even though they are not ordered.

for Favorite menu

select t.mid, t.name, sum(number_of_order) as total_order 
from TAB007 t LEFT JOIN post p ON p.mid = t.mid 
group by t.mid, t.name 
order by total_order desc;

for Highest benefit menu

select t.mid, t.name, sum(number_of_order)*(t.price - t.expanse) as benefit 
from TAB007 t LEFT JOIN post p ON p.mid = t.mid 
group by t.mid, t.name 
order by benefit desc;

Joining Tables In SQL with no success

4 Answers4