mysql> select * from produkty;
+----+------+---------+-------------+----------+----------------+
| id | kcal | protein | weglowodany | tluszcze | nazwa produktu |
+----+------+---------+-------------+----------+----------------+
| 1 | 155 | 13 | 1 | 1 | jajo |
| 2 | 89 | 1 | 23 | 0 | banan |
| 3 | 271 | 25 | 0 | 19 | stek |
+----+------+---------+-------------+----------+----------------+
mysql> select * from meal;
+----+---------------+-------------+
| id | nazwa_posilku | typ_posilku |
+----+---------------+-------------+
| 1 | sniadanie | proteinowe |
| 2 | obiad | proteinowe |
+----+---------------+-------------+
mysql> select * from dodanie_produktu_do_posilku;
+----+------------+-------------+----------+
| id | id_posilku | id_produktu | quantity |
+----+------------+-------------+----------+
| 1 | 1 | 1 | 100 |
| 2 | 1 | 2 | 100 |
| 3 | 2 | 3 | 50 |
+----+------------+-------------+----------+
mysql> select * from users;
+----+---------+--------------------+-------------------------+
| id | id_user | czy_zostal_spozyty | ID_posilku_z_produktami |
+----+---------+--------------------+-------------------------+
| 1 | 89 | true | 1 |
| 2 | 89 | false | 2 |
| 3 | 8 | true | 2 |
| 4 | 4 | true | 3 |
| 5 | 3 | false | 1 |
| 6 | 59 | true | 1 |
| 7 | 450 | true | 3 |
+----+---------+--------------------+-------------------------+
Potestuj te zapytanie:
select kcal, protein, weglowodany, tluszcze, quantity,
truncate((kcal + protein + weglowodany + tluszcze)*(quantity/1000),2) as res,
meal.nazwa_posilku, meal.typ_posilku, produkty.`nazwa produktu`, users.id_user
from produkty,users, meal,dodanie_produktu_do_posilku where (case when czy_zostal_spozyty = 'true' then users.id_posilku_z_produktami end)
= dodanie_produktu_do_posilku.id and dodanie_produktu_do_posilku.id_posilku = meal.id and dodanie_produktu_do_posilku.id_produktu=produkty.id;
Rezultat tego zapytania dla tych danych, co wyżej podałem:
+------+---------+-------------+----------+----------+-------+---------------+-------------+----------------+---------+
| kcal | protein | weglowodany | tluszcze | quantity | res | nazwa_posilku | typ_posilku | nazwa produktu | id_user |
+------+---------+-------------+----------+----------+-------+---------------+-------------+----------------+---------+
| 155 | 13 | 1 | 1 | 100 | 17.00 | sniadanie | proteinowe | jajo | 89 |
| 89 | 1 | 23 | 0 | 100 | 11.30 | sniadanie | proteinowe | banan | 8 |
| 271 | 25 | 0 | 19 | 50 | 15.75 | obiad | proteinowe | stek | 4 |
| 155 | 13 | 1 | 1 | 100 | 17.00 | sniadanie | proteinowe | jajo | 59 |
| 271 | 25 | 0 | 19 | 50 | 15.75 | obiad | proteinowe | stek | 450 |
+------+---------+-------------+----------+----------+-------+---------------+-------------+----------------+---------+