The title is indeed terrible but I have no idea what to put. I am working on a Bill of Materials app and I'm starting out with the database layout and the REST API to interact with the database.
I currently have four tables but the query I want to write involves three of them
Now what I want to do is list all the products and for each product calculate the cost of all the components that product needs. So if a product needs 4 doodads that cost $1 and 7 whatzits that cost $2 the cost of the product would be $18 (41 + 72). I know I'd need some JOINs but I have no idea what I'd need.
SELECT p.name AS product_name, SUM(pc.count * c.price) AS cost
FROM products p
JOIN product_components pc ON p.id = pc.product_id
JOIN components c ON pc.component_id = c.id
GROUP BY p.id;
select pc.product_id, sum(price) from products p, product_components pc, products p where p.id = pc.product_id and pc.component_id = c.id group by pc.product_id;
Your first solution didn't work, Error Code: 1066. Not unique table/alias: 'p' and your second solution gave a sum of 3; looks like it's just counting the number of components and not calculating the cost.