I have a question about my ql join Help me please. crying

Hello, I am a 21-year-old young man who is working as a service planner and studying mysql.

I'm just asking you a question because I couldn't get an answer because I was stuck in the middle of my ql assignment ㅠ<

It's a task I'm working on, and I have to make this table.

select A.order_idx, A.amount_before_discount, (A.amount_before_discount + A.delivery_cost - A.totail_price) as discount_amount, A.delivery_cost, A.total_price from(select order_idx, (m.price * od.amount)as amount_before_discount, om.delivery_fee, om.total_price from order_detail as od left join menu as m on od.menu_idx = m.idx left join order_meta as om on od.idx = om.idx)A

This is the code I've written so far, and 1054 errors keep popping up, so I'm asking you a question because I don't know even if I look it up

I'm sorry to ask you this question because it's only been a week since I started mysql

To make it easier for you to understand, I made dummy data into an Excel sheet.

mysql join

2022-09-22 08:12

2 Answers

And the answer above is

Tutorial point

This is the answer written after checking that there was no error by putting the following fingerprint in and executing it.

BEGIN TRANSACTION;

create table order_detail(
    idx integer primary key,
    order_idx integer,
    menu_idx integer,
    amount integer
);

create table menu(
    idx integer primary key,
    price integer
);

create table order_meta(
    idx integer primary key,
    order_idx integer,
    delivery_fee integer,
    total_price integer
);

insert into order_detail values(1,1,13,1);
insert into order_detail values(2,1,15,2);
insert into order_detail values(3,2,13,2);

insert into menu values(13,10000);
insert into menu values(14,12000);
insert into menu values(15,11000);

insert into order_meta values(1, 1, 3000, 10000);
insert into order_meta values(2, 2, 1000, 20000);
COMMIT;

select order_detail.order_idx, sum(amount*price) as amount_before_discount,
    sum(amount*price) + delivery_fee - total_price as discount_amount,
    delivery_fee, total_price
from order_meta, 
    order_detail join menu
    on order_detail.menu_idx = menu.idx
where order_meta.order_idx = order_detail.order_idx
group by order_detail.order_idx, delivery_fee, total_price;

2022-09-22 08:12

I'll answer with that in mind.

It seems the most difficult to find the amount_before_discount according to order_idx, so let's solve this first.

To obtain this value

select order_idx, sum(amount*price) as amount_before_discount
from order_detail join menu
on order_detail.menu_idx = menu.idx
group by order_idx

For the delivery fee, you can add the order_meta table to the above query.

select order_detail.order_idx, sum(amount*price) as amount_before_discount, delivery_fee
from order_meta, #adding
    order_detail join menu
    on order_detail.menu_idx = menu.idx
where order_meta.order_idx = order_detail.order_idx
group by order_detail.order_idx, add delivery_fe #

select order_detail.order_idx, sum(amount*price) as amount_before_discount, delivery_fee, total_price
from order_meta, 
    order_detail join menu
    on order_detail.menu_idx = menu.idx
where order_meta.order_idx = order_detail.order_idx
Add group by order_detail.order_idx, delivery_fee, total_price #

select order_detail.order_idx, sum(amount*price) as amount_before_discount,
    sum(amount*price) + delivery_fee - total_price as discount_amount, #add
    delivery_fee, total_price
from order_meta, 
    order_detail join menu
    on order_detail.menu_idx = menu.idx
where order_meta.order_idx = order_detail.order_idx
group by order_detail.order_idx, delivery_fee, total_price

2022-09-22 08:12

If you have any answers or tips

Popular Tags

python x 4647

android x 1593

java x 1494

javascript x 1427

c x 927

c++ x 878

ruby-on-rails x 696

php x 692

python3 x 685

html x 656