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
CREATE TABLE `components` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(64) NOT NULL,
`description` text DEFAULT NULL,
`price` float unsigned NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `name_UNIQUE` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE `products` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(64) NOT NULL,
`description` text DEFAULT NULL,
`tax_code` varchar(8) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `name_UNIQUE` (`name`),
KEY `name_idx` (`tax_code`),
CONSTRAINT `name` FOREIGN KEY (`tax_code`) REFERENCES `tax_codes` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE `product_components` (
`product_id` int(10) unsigned NOT NULL,
`component_id` int(10) unsigned NOT NULL,
`count` int(10) unsigned NOT NULL,
PRIMARY KEY (`product_id`,`component_id`),
KEY `fk_component_id_idx` (`component_id`),
CONSTRAINT `fk_component_id` FOREIGN KEY (`component_id`) REFERENCES `components` (`id`),
CONSTRAINT `fk_product_id` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
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.
You must log in or register to comment.
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;Try this, I take no responsibility for it though.
Trick is to join everything and sum the price of the component with the required quantity on the products_components table, and group by product so that it does so by product.
Probably something like this (syntax may be incorrect for MySQL, I work mainly with Oracle):
select p.name, sum(c.price) from products p, product_components pc, products p where p.id = pc.product_id and pc.component_id = c.id group by p.name;Edit: Here’s the equivalent ANSI SQL syntax… I think:
select products.name, sum(component.price) from products join product_components on products.id = product_components.product_id join components on product_components.component_id = components.id group by products.name;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.It’s been edited a few times, try the latest again.
Yup it works thanks!
Side note, does the page say there’s 17 comments on this post?
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.
My answer was wrong. Ignore me.
I highly recommend trying Stackoverflow. :)
