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
    4·
    1 year ago

    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(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
      1·
      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.

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

            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.

  • SlothStyle@lemmy.worldEnglish
    2·
    1 year ago

    That sounds kind of like putting business logic in your data layer. I generally try to avoid that but you could do something like

    SELECT ( :Doodads_amt * case when id=:doodad_id then price else 0 end ) + (:whatzits_amt * case when id=:whitzits_id then price else 0 end ) from components where component id in (:doodad_id, :whatzits_id);

    That’s probably not exactly it but you can work with the case statements to filter the data you need. I can’t remember off the top of my head what you want in your else statement

    The words with a colon at the front are bind variables.

    What might be easier is to just query for the price of each item then multiply that price by an amount in your API.

    Let me know if you have questions.