Need help writing an SQL query

Programming@beehaw.org – 4 points – 1 years ago

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.

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.

I think your solution works, with the test data in my database I got a product cost of 18.

Glad to have helped!

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.

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 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.

I highly recommend trying Stackoverflow. :)