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 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.
Side note, does the page say there’s 17 comments on this post for you?
No, for me it says there are 7 comments but that's still incorrect. When I manually count them I see 5 comments, not including the one I'm writing. I suspect edits might be getting counted as "new posts" by some instances. I gooned up the SQL pretty bad on the first attempt and edited it several times. There maybe something funky going on under the hood with federation as well that would explain why we're getting different incorrect counts.