jhon jhon - 4 months ago 7
MySQL Question

Sql Join Three table

Here is my table ..and i want this output:

pid | pname | custname | quantity | total base price | sale price | profit


schema:

create table customers
(
cid int,
cname varchar(1000),
cg varchar(1000)
)

create table prod
(
pid int,
pname varchar(1000),
baseprice int,
saleprice int
)

create table orders
(
oid int,
custid int,
pid int,
quantity int,
odate date
)


How do I write a query for this?

Answer
--pid | pname | custname | quantity | total base price | sale price | profit
select o.pid, p.pname, c.cname AS custname, SUM(o.quantity) AS quantity, 
SUM(p.baseprice) AS 'total base price', SUM(p.saleprice) AS 'sale price', 
SUM(p.baseprice) - SUM(p.saleprice) AS profit -- change this math to what you need
from orders o join prod p
on o.pid = p.pid
join customers c
on o.custid = c.cid
group by o.pid, p.pname, c.cname