Gertjan Brouwer Gertjan Brouwer - 1 month ago 13
MySQL Question

SQL inner join select field list

I have 2 tables, 1 is called

menu
and 1 is called
orderregel
.
An
orderregel
is a database table used in stores to store specific products and the amount.

I have this for menus in a restaurant, people can order different menus and they will all get their own orderregel which then is added to an order which you pay at the end.

So I want to see the the menus which are chosen per table and the amount they are chosen.

Here are the 2 tables:

|---------------------|-------------------------|
| Menu | Orderregel |
|---------------------|-------------------------|
| ID | Name | ID | Quantity | Menu_id |
|---------------------|-------------------------|
| 1 | Menu One | 1 | 3 | 1 |
| 2 | Menu two | 2 | 2 | 1 |
| 3 | Menu Three | 3 | 4 | 3 |
|---------------------|-------------------------|


And with a SQL query I want to get this result:

|---------------------|
| Result of query |
|---------------------|
| Menu Name|Quantity |
|---------------------|
| Menu One | 5 |
| Menu Two | 0 |
|Menu Three| 4 |
|---------------------|


The SQL query I currently have is:

SELECT SUM(orderregel.aantal) AS Aantal
FROM `orderregel`
WHERE menu_id IS NOT null
GROUP BY menu_id


Which will give me this result

|---------------------|
| Result of query |
|---------------------|
| Quantity |
|---------------------|
| 5 |
| 4 |
|---------------------|


I am now trying to figure out a query which will get the name and the quantity as shown in the table above this one. What i tried to do is this :

SELECT menu.naam, quantity.aantal
FROM `menu`
INNER JOIN
(SELECT sum(orderregel.aantal) AS aantal
FROM orderregel
WHERE menu_id IS NOT null
GROUP BY menu_id) AS quantity


But this query will give me duplicates in a weird way, like this:

|---------------------|
| Result of query |
|---------------------|
| Menu Name|Quantity |
|---------------------|
| Menu One | 5 |
| Menu Two | 0 |
|Menu Three| 5 |
| Menu One | 4 |
| Menu Two | 0 |
|Menu Three| 4 |
|---------------------|


Do you have a solution? Thanks

EDIT 1
I forgot to mention i use MySQL and i tried to LEFT JOIN and OUTER JOIN which both give the erro 1062

EDIT 2

SELECT menu.naam as 'Menu naam', sum(orderregel.aantal) as Aantal
FROM `menu`
LEFT JOIN orderregel on menu.id = orderregel. menu_id
WHERE order_id = '57'
GROUP BY menu_id


the order_id = '57' will be changed in my code to a variable based on the selected order/customer. Thank you guys for helping

Answer

Looking to your tables you should only permform a select with group by

  SELECT menu.name, sum(orderregel.quantity)    as quantity
  FROM `menu` 
  INNER JOIN orderregel
  on menu.menu_id = orderregel. menu_id
  group by menu_id 

or if the join clause don't always match use left join

  SELECT menu.name, sum(orderregel.quantity)    as quantity
  FROM `menu` 
  LEFT JOIN orderregel
  on menu.menu_id = orderregel. menu_id
  group by menu_id 
Comments