CODEWITHSUNDEEP
mysqlsqlmysql-workbench
Tomimotmot
Tomimotmot

Reputation: 49

sql query works only for 1 record

I'am really new in sql - since 1 week, and my query works only for 1 record

Select m.meal_id, m.name, m.usp, m.description, m.worktime, m.proprietor, m.img, m.url, m.servings, d.name, c.name, s.name, sum(i.price / i.minamount * r.quantity) as mealPrice

from meals m
join recipe r on m.meal_id = r.meal_id
join ingredients i on i.ingredient_id = r.ingredient_id
join difficulty d on d.difficulty_id = m.difficulty_id
join cat c on c.cat_id = m.cat_id
join social s on s.social_id = m.social_id
and m.meal_id = 1

This query works for one record, but if I want to have all the meals (without the "and" function) I still get only one record but the sum mealPrice is for all records added, how do I get this query for all meals?

I like for each meal one record with the total sum mealPrice

my tables:

    create table meals
(
    meal_id int unsigned not null auto_increment primary key,
    name varchar(100) not null,
    usp varchar(100) not null,
    description varchar(500) not null,
    worktime varchar(100) not null,
    proprietor varchar(100) not null,
    img varchar(100) not null,
    url text not null,
    servings int not null,
    difficulty_id int unsigned not null,
    cat_id int unsigned not null,
    social_id int unsigned not null,
    foreign key (difficulty_id) references difficulty (difficulty_id),
    foreign key (cat_id) references cat (cat_id),
    foreign key (social_id) references social (social_id)
);

create table ingredients
(
    ingredient_id   int unsigned not null auto_increment primary key,
    name varchar(45) not null,
    minamount float not null,
    price float not null
);

create table recipe
(
    meal_id int unsigned not null,
    ingredient_id int unsigned not null,
    quantity float,
    primary key (meal_id, ingredient_id),
    foreign key (meal_id) references meals (meal_id),
    foreign key (ingredient_id) references ingredients (ingredient_id)

);

Upvotes: 1

Views: 63

Answers (2)

Madhur Bhaiya
Madhur Bhaiya

Reputation: 28844

EDIT: Based on recent edit by OP; you need to simply do a GROUP BY.

Try:

Select m.meal_id, 
       m.name, 
       m.usp, 
       m.description, 
       m.worktime, 
       m.proprietor, 
       m.img, 
       m.url, 
       m.servings, 
       d.name, 
       c.name, 
       s.name, 
       SUM(i.price / i.minamount * r.quantity) as mealPrice
from meals m
join recipe r on m.meal_id = r.meal_id
join ingredients i on i.ingredient_id = r.ingredient_id
join difficulty d on d.difficulty_id = m.difficulty_id
join cat c on c.cat_id = m.cat_id
join social s on s.social_id = m.social_id 
GROUP BY m.meal_id, 
         m.name, 
         m.usp, 
         m.description, 
         m.worktime, 
         m.proprietor, 
         m.img, 
         m.url, 
         m.servings, 
         d.name, 
         c.name, 
         s.name

Based on Previous Version of OP's Problem:

This is a Window function problem. In newer versions of MySQL (>= 8.0), you can easily do this. In older version (your version is 5.5), we can solve it using Session Variables.

Try:

Select m.meal_id, 
       m.name, 
       m.usp, 
       m.description, 
       m.worktime, 
       m.proprietor, 
       m.img, 
       m.url, 
       m.servings, 
       d.name, 
       c.name, 
       s.name, 
       (@sum := @sum + (i.price / i.minamount * r.quantity)) as mealPrice
from meals m
join recipe r on m.meal_id = r.meal_id
join ingredients i on i.ingredient_id = r.ingredient_id
join difficulty d on d.difficulty_id = m.difficulty_id
join cat c on c.cat_id = m.cat_id
join social s on s.social_id = m.social_id 
CROSS JOIN (select @sum := 0) AS init

In MySQL version >= 8.0, we could use SUM() as Window function, over a partition of complete resultset. In that case, query would be:

Select m.meal_id, 
       m.name, 
       m.usp, 
       m.description, 
       m.worktime, 
       m.proprietor, 
       m.img, 
       m.url, 
       m.servings, 
       d.name, 
       c.name, 
       s.name, 
       SUM(i.price / i.minamount * r.quantity) OVER() as mealPrice
from meals m
join recipe r on m.meal_id = r.meal_id
join ingredients i on i.ingredient_id = r.ingredient_id
join difficulty d on d.difficulty_id = m.difficulty_id
join cat c on c.cat_id = m.cat_id
join social s on s.social_id = m.social_id

Upvotes: 0

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 31991

you need below query ,cross join helps to you get the desire result

Select m.meal_id, 
       m.name, 
       m.usp, 
       m.description, 
       m.worktime, 
       m.proprietor, 
       m.img, 
       m.url, 
       m.servings, 
       d.name, 
       c.name, 
       s.name, 
       (@s := @s + (i.price / i.minamount * r.quantity)) as mealPrice

from meals m
join recipe r on m.meal_id = r.meal_id
join ingredients i on i.ingredient_id = r.ingredient_id
join difficulty d on d.difficulty_id = m.difficulty_id
join cat c on c.cat_id = m.cat_id
join social s on s.social_id = m.social_id 
CROSS JOIN ( select @s:= 0) AS t

Upvotes: 0

Related Questions