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.
It’s been edited a few times, try the latest again.
Yup it works thanks!