[SOLVED] calculation from 2 tables help please

[spick]

I'm a little new to this so I need a little help and I'm using mysql 5. I have these 2 tables and I'm trying figure out the total sales of each product.  I assume there's gonna be calculations, but the thing is I have no idea about how to do the calculation.  So if someone can help me out with this that would be awesome or point me to the right direction.

 

orderdetails

(OID number(6),

PID int(4),

Qty ing(1));

 

products

(PID int(2),

Pname varchar(20),

Price numeric(5,2),

OnHand int(3),

primary key(PID));

 

 

Here is the batch file I use to create the database for Oracle cuz I accidently deleted the mysql batch file and didn't want to type it up again, but they're pretty similar anyways.

drop table orderdetails;
create table orderdetails(OID number(6), PID number(4), Qty number(1));
insert into orderdetails values('987654','10','1');
insert into orderdetails values('987653','11','2');
insert into orderdetails values('943255','11','3');
insert into orderdetails values('943255','19','4');
insert into orderdetails values('977575','12','5');
insert into orderdetails values('987654','15','1');
insert into orderdetails values('944352','12','2');
insert into orderdetails values('987654','15','3');
insert into orderdetails values('989423','19','4');
insert into orderdetails values('932466','19','5');
insert into orderdetails values('929840','15','1');
insert into orderdetails values('977575','12','2');
insert into orderdetails values('987654','11','3');
insert into orderdetails values('929840','19','4');
insert into orderdetails values('929840','10','5');

drop table products;
create table products(PID number(2), Pname varchar(20), Price numeric(5,2), OnHand number(3), primary key(PID));
insert into products values('10','sony phone','99.99','20');
insert into products values('11','motorola phone','49.99','25');
insert into products values('19','samsung phone','29.99','15');
insert into products values('15','apple iphone','299.99','30');
insert into products values('12','palm treo','199.99','18');

 

[spick]

all I can get is this, it doesn't factor in the quantity of items in the orderdetails.

 

select pid, pname, sum(price) from products natural join(orderdetails) group by pid, pname;

[zenag]

 

try left join...

[fenway]

For example:

 

select pid, pname, sum(price) from products left join orderdetails using (PID ) group by pid, pname;

[spick]

I figure it out after searching around the forum and messing with it.  If anyone know how to do this using subquery and any other way without using natural join let me know cuz I don't like using natural.

 

select pid, pname, sum(price*(qty)) as totalsales from products natural join orderdetails group by pid, pname;

[fenway]

You can leave out the word natural, it doesn't do anything in this context.