Nicu Mihai Nicu Mihai - 5 months ago 18
SQL Question

Mysql multiple tables

I have 5 tables containg products and one table containg the order.

CREATE TABLE `Prod1` (
`idProd1` INT NOT NULL,
`name` VARCHAR(45) NULL,
`price` INT NULL,
PRIMARY KEY (`idProd1`));


This is model for one table. I have 5 like this one.

In the 6 i have foreign key all the primary key from the products table
I want to create a select to give me the total to pay.

The problem is if one of the key is null is returning me the total from all the tables.

This is what i have ti'll now.

select p1.idprod1,p2.idprod2,p3.idprod3,p4.idprod4,p5.idprod5, sum(p1.idprod1+p2.idprod2+p3.idprod3+p4.idprod4+p5.idprod5)
from prod1 p1,prod2 p2,prod3 p3,prod4 p4,prod5 p5,order o
where p1.idprod1=o.iprod1 and p2.idprod2=o.idprod2 and p3.idprod3=o.idprod3 and p4.idprod=o.idprod4 and p5.idprod5=o.idprod5 and
o.idorder=(select max(idorder) from order);

Answer

Since you have where p1.idprod1=o.iprod1 and p2.idprod2=o.idprod2 and p3.idprod3=o.idprod3 and p4.idprod=o.idprod4 and p5.idprod5=o.idprod5 I don't see any reason to return p1.idprod1,p2.idprod2,p3.idprod3,p4.idprod4,p5.idprod5.

Other note what is the reason to sum(p1.idprod1+p2.idprod2+p3.idprod3+p4.idprod4+p5.idprod5)?

I think the only column we can SUM is the price.

So my approach is:

SELECT 
  o.*,
  sum(p1.price+p2.price+p3.price+p4.price+p5.price)
FROM order o
LEFT JOIN prod1 p1
ON p1.idprod1=o.iprod1
LEFT JOIN prod2 p2
p2.idprod2=o.idprod2
LEFT JOIN prod3 p3
ON p3.idprod3=o.idprod3 
LEFT JOIN prod4 p4
ON p4.idprod=o.idprod4
LEFT JOIN prod5 p5
ON p5.idprod5=o.idprod5
WHERE o.idorder=(select max(idorder) from order)
GROUP BY o.idorder
Comments