I have a hard time figuring this one out. And i apologize if the answer is out there, i have searched through all of stackoverflow.
I have an order system, where i order by tableID, and continue to make rows in my Database if someone orders stuff. My problem is getting my data out on 1 row, in this example the table ID, so all the ordered stuff, shows on 1 line only.
I have 3 tables, Food, Drink, Dessert, all with a foreign key in my OrderTable.
id fk_tableid fk_drinkId fk_foodId fk_dessId amount 1 5 2 0 0 2 2 5 0 1 0 1 3 5 0 2 0 1 4 5 0 0 2 2 11 8 1 0 0 2 21 1 1 0 0 5 22 1 0 1 0 9 23 1 0 0 1 2
By a normal select, with left joins, i can get the data out on multiple rows, like this where i get those with tableId 5 and showing the name of the ordered consumable also:
id fk_tableId fk_drinkId fk_foodId fk_dessId amount foodName drinkName dessertName 1 5 2 0 0 2 NULL Sodavand NULL 2 5 0 1 0 1 Lasagne NULL NULL 3 5 0 2 0 1 Pizza NULL NULL 4 5 0 0 2 2 NULL NULL Softice
I tried using group_concat also, which put data on 1 line, but it seems to put everything on 1 line, not just grouped by tableId.
How i want it to be is something like this (the 2x Lasagne for example, is just how i want it to look at the site, but maybe i need to use 1xLasagne twice instead. It would just look messy with 1x Beer 10 times.):
fk_tableId fk_drinkId fk_foodId fk_dessId foodName drinkName dessertName fulllPrice 5 2,2 1,2 2,2 Pizza,Lasagne 2xSodavand 2xSoftice 195
I am aware my question might be wrongly formatted, but i also have a hard time 100% knowing what to google and search for here. I have tried for 2 days, and even asked my teacher who could not do it, he was doing something with CASES and sum(), but it did not work out either.
Any help will be much appreciated!
UPDATE:
Added SQL Query:
SELECT
menukort_ordre.id,
fk_tableId,
fk_drinkId,
fk_foodId,
fk_dessId,
amount,
menukort_food.name AS foodName,
menukort_drink.name AS drinkName,
menukort_dessert.name AS dessertName
FROM menukort_ordre
LEFT Join menukort_drink
ON (menukort_ordre.fk_drinkId = menukort_drink.id)
LEFT Join menukort_food
ON (menukort_ordre.fk_foodId = menukort_food.id)
LEFT Join menukort_dessert
ON (menukort_ordre.fk_dessId = menukort_dessert.id)
WHERE fk_tableid = fk_tableid
With GROUP_CONCAT i tried to do this instead, which put it on 1 row, but due to my WHERE, i get all data on 1 row.
GROUP_CONCAT(menukort_food.name ) AS foodName,
GROUP_CONCAT(menukort_drink.name) AS drinkName,
GROUP_CONCAT(menukort_dessert.name) AS dessertName
UPDATE:
First off I changed your database design since there was no need for 3 tables like that unless you really wanted them to be separated as such. I understand wanting to separate data, but there are times to do so and times not to do it. I understand since my personal database project has me breaking everything up. So the below solution will be based off of my design which is as follows.
Code or ID (PK) Category
This table will be a lookup table just to make sure drink and food and desert is spelled correctly. Frankly you don't need it unless you need that information specific and want it to be correct.
Next will be the table that stores the drinks, deserts, and food
ID serial Category Name Price
and final the order table that will keep track of the orders
BillID TableNum ItemNum (fk) ID (PK)
This way you can keep track of which table the food goes to and each check or bill. Your design was fine if you wanted to find out how much each table made in a day, but I'm assuming like an actual restaurant you would want to know for each bill. With this you can have multiple orders of a coke or whatever at the same table on the same bill.
Now on to the solution.
This doesn't have the count, but could work on it if you really need it, but frankly I think it is pointless to have a count unless you are going to ungroup the results and have something like this:
tableNum BillNum ItemNum ItemName 1 1 1 Coke 1 1 2 Steak 1 1 3 Pasta 1 1 1 Coke
then you could end up with something like this
tableNum BillNum ItemNum ItemName TimesBy 1 1 1 Coke 2 1 1 2 Steak 1 1 1 3 Pasta 1
The SQL CODE below will give you what you need I believe. I'm using my version of the database and I think you should too just because it is easier and there is no point to having 3 tables for each thing.
CREATE TEMPORARY TABLE IF NOT EXISTS table2 AS (
select BillID, tablenum,ItemNum,Items.name,Items.price
from Orders, Items
where Orders.ItemNum=Items.id
);
create TEMPORARY TABLE IF NOT EXISTS table3 AS (
select SUM(price) as total, BillID
from table2
group by BillID
);
select table3.BillID, TableNum, GROUP_CONCAT(ItemNum order by ItemNum ASC) as ItemNum, GROUP_CONCAT(name order by name ASC) as Item, GROUP_CONCAT(price order by name asc) as ItemPrice, total
from table2, table3
where table2.BillID=table3.BillID
group by BillID;
DROP TABLE IF EXISTS table2;
DROP TABLE IF EXISTS table3;
A few other solutions would be to look into using something like php (programming) to help with this or a stored procedure.
If you need an explanation just ask. Also I'm curious is this for homework or a project? I just want to know why your doing this?
Hope it helps.