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.

  • maynarkh@feddit.nlEnglish
    7·
    1 year ago
    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.

  • CountVon@sh.itjust.worksEnglish
    2·
    1 year ago

    Probably something like this (syntax may be incorrect for MySQL, I work mainly with Oracle):

    select p.name, sum(pc.count * c.price)
from products p, product_components pc, components c
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(product_components.count * components.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;
    • UntouchedWagons@lemmy.caOPEnglish
      0·
      1 year ago

      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.