I have 3 tables: products, product_options and product_option_choices. Products can have multiple options, options can have multiple choices.
Choices can adjust the main product price (choice_price_adjust field).
What I'm trying to do is fetch the products and have a field returned on each product that is the maximum possible adjustment value and the minimum possible adjustment value so I know the range of prices this product can be sold at.
This is what I have so far but I seem unable to reference the prod_id field (or even an alias) of the products table within the subquery.
If I give a specific product id in place of pid it works but obviously I want to subquery running based on each individual product.
I'm also open to suggestions on how to improve the whole query without needing to find a solution to this particular issue.
SELECT *, p.prod_id as pid,
(SELECT SUM(y.min) as min_total
FROM (
SELECT x.min as min
FROM (
SELECT product_option_choices.prod_op_id as op_id, MIN(choice_price_adjust) as min
FROM product_option_choices
WHERE prod_id = pid
GROUP BY product_option_choices.prod_op_id
) x
GROUP BY x.op_id
) y) as choices_min_price
FROM products p
--
EDIT to clarify data and expected results.
The database structure (simplified to only relevant test data and I haven't added FKs) is as follows:
CREATE TABLE `products` (
`prod_id` int(9) UNSIGNED NOT NULL,
`product_name` varchar(255) DEFAULT NULL,
`price` int(16) UNSIGNED DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
INSERT INTO `products` (`prod_id`, `product_name`, `price`) VALUES
(1, 'Product 1', 1000),
(2, 'Product 2', 2000);
CREATE TABLE `product_options` (
`prod_op_id` int(9) UNSIGNED NOT NULL,
`prod_op_name` varchar(255) NOT NULL,
`prod_id` int(9) UNSIGNED NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
INSERT INTO `product_options` (`prod_op_id`, `prod_op_name`, `prod_id`) VALUES
(1, 'Color', 1),
(2, 'Size', 1),
(3, 'Grade', 1),
(4, 'Direction', 2),
(5, 'Smell', 2),
(6, 'Origin', 2);
CREATE TABLE `product_option_choices` (
`choice_id` int(16) UNSIGNED NOT NULL,
`prod_op_id` int(9) UNSIGNED NOT NULL,
`prod_id` int(9) UNSIGNED NOT NULL,
`choice_name` varchar(255) NOT NULL,
`choice_price_adjust` int(10) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
INSERT INTO `product_option_choices` (`choice_id`, `prod_op_id`, `prod_id`, `choice_name`, `choice_price_adjust`) VALUES
(1, 1, 1, 'Purple', 100),
(2, 1, 1, 'Orange', -500),
(3, 2, 1, 'Small', -300),
(4, 2, 1, 'Large', 400),
(5, 3, 1, 'A', 900),
(6, 3, 1, 'B', 100),
(7, 4, 2, 'Up', 200),
(8, 4, 2, 'Down', -200),
(9, 5, 2, 'Vanilla', -150),
(10, 5, 2, 'Sandlewood', 0),
(11, 6, 2, 'Afica', 0),
(12, 6, 2, 'Norway', 700);
ALTER TABLE `products`
ADD PRIMARY KEY (`prod_id`);
ALTER TABLE `product_options`
ADD PRIMARY KEY (`prod_op_id`);
ALTER TABLE `product_option_choices`
ADD PRIMARY KEY (`choice_id`);
Each product has its own set of options. Each of those options has their own set of choices. In the front end, one choice per option can be selected by users (a selection for each option is required).
What I'm trying to have returned by the query is a row for each product with the product table data (product_name, price) but also a calculated column for both the maximum and minimum possible totals for 'choice_price_adjust'.
For example, with product 1 the user has to select a value for Color, Size and Grade. They cannot select a value for Direction, Smell or Origin because those options are for product 2.
The user has a choice of: Purple and Orange for Color; Small and Large for Size; A and B for Grade. I only added 2 in each but there can be 1 or more, it is not always 2 choices.
So, for Product 1's returned row I would want the query to look at the available values for Color (Purple and Orange). It then grabs the lowest value within those choices as the lowest_value (in this case, Orange is lower at -500 so that's the lowest_value). It also grabs the highest value within those choices (100 in this case from Purple) and that's the highest_value. Same with Size (in this case, -300 is lowest, 400 is highest). Same for Grade (100 is lowest, 900 is highest). All other choices and options are irrelevant because they do NOT have a prod_id value of 1 (Product 1).
I'd then like the total of the lowest_value numbers (as min_total) and the total of the highest_value numbers (as max_total) to be added as a column in the product row that gets returned along with prod_id, product_name and price for each product. In the above case, min_total would be -700 (-500 + -300 + 100). max_total would be 1,400 (100 + 400 + 900)
My latest attempt based on feedback was to add the subquery to the FROM and consider JOINs but it's still not right as it is doing the SUM on ALL choices and not the subset I'm trying to pick for each product. I'm using the test data for this now:
SELECT p.prod_id, p.product_name, p.price, SUM(x.min_val) as min_total, SUM(x.max_val) as max_total
FROM products p, (
SELECT
poc.prod_id,
poc.choice_id,
MIN(poc.choice_price_adjust) as min_val,
MAX(poc.choice_price_adjust) as max_val
FROM product_option_choices poc
LEFT JOIN product_options po on(po.prod_op_id = poc.prod_op_id)
LEFT JOIN products p2 on(p2.prod_id = poc.prod_id)
GROUP BY poc.choice_id
) x
GROUP BY p.prod_id;
When I try to add WHERE p2.prod_id = p.prod_id I'm told p.prod_id is an unknown column. I thought I could use this as it's only within a single subquery now? Or is this because of the order in which subqueries run based on which part of the main query they are in?
ALTERNATIVE ANSWER:
Since there were some missunderstandings about the expected result and since it seams that the result should depend on user provided selection of product id and/or option id here is the answer where the resultset and lowest/largest values depend on user's selections.
Sample data and this code could be found and played with in the fiddle here.
/*
Try to change the values of the variables @usr_prod_id And @usr_prod_op_id
The variables here play the role of user selections for fetching the data
VARIABLES SETTINGS:
1. If both ids are 0 or Null - result is all products with TOTAL values
2. If product id = 0 or Null and option id > 0 result is selected option with option values
3, If product id > 0 And option id = 0 or Null result is selected product with all options and option values
4. If product id > 0 And option id > 0 result is selected option with option values (same as 2.)
5. If any id is less then 0 - there will be no rows selected
*/
SET @usr_prod_id = 0,
@usr_prod_op_id = 0;
WITH
usr_selection as
( Select Case When Coalesce(@usr_prod_id, 0) = 0 And
Coalesce(@usr_prod_op_id , 0) = 0
Then 'PRODS'
When Coalesce(@usr_prod_id, 0) = 0 And
Coalesce(@usr_prod_op_id , 0) > 0
Then 'OPTS'
When Coalesce(@usr_prod_id, 0) > 0 And
Coalesce(@usr_prod_op_id , 0) = 0
Then 'OPTS'
When Coalesce(@usr_prod_id, 0) > 0 And
Coalesce(@usr_prod_op_id , 0) > 0
Then 'OPTS'
Else 'ERROR'
End as flag
),
grid AS
( Select usr.flag, p.prod_id, p.product_name, p.price,
po.prod_op_id, po.prod_op_name,
poc.choice_name, poc.choice_price_adjust
From product_options po
Inner Join usr_selection usr ON( usr.flag != 'ERROR' )
Left Join product_option_choices poc ON(poc.prod_id = po.prod_id And poc.prod_op_id = po.prod_op_id)
Left Join products p ON(p.prod_id = po.prod_id)
),
adjustments as
( Select flag, prod_id, product_name, price, prod_op_id, prod_op_name,
Min(choice_price_adjust) as lowest_price_adjust,
Max(choice_price_adjust) as largest_price_adjust
From grid
Group By flag, prod_id, product_name, prod_op_id, prod_op_name, price
)
Select Distinct
a.prod_id, a.product_name, a.price,
Case When flag = 'PRODS'
Then Sum(a.lowest_price_adjust)
Over(Partition By prod_id)
When flag = 'OPTS'
Then lowest_price_adjust
End as lowest,
Case When flag = 'PRODS'
Then Sum(a.largest_price_adjust)
Over(Partition By prod_id)
When flag = 'OPTS'
Then largest_price_adjust
End as largest,
Case When flag = 'PRODS' Then Concat('Total - ', a.product_name)
When flag = 'OPTS' Then Concat('Option - ', a.prod_op_name)
End as source_of_values
From adjustments a
Where a.prod_id = Case When Coalesce(@usr_prod_id, 0) > 0
Then @usr_prod_id
Else a.prod_id
End
And
a.prod_op_id = Case When Coalesce(@usr_prod_op_id, 0) > 0
Then @usr_prod_op_id
Else a.prod_op_id
End
Order By prod_id, product_name
/* R e s u l t : ( for @usr_prod_id = 0 And @usr_prod_op_id = 0 )
prod_id product_name price lowest largest source_of_values
------- ------------- ------ ------ ------- --------------------
1 Product 1 1000 -700 1400 Total - Product 1
2 Product 2 2000 -350 900 Total - Product 2 */
/* R e s u l t : ( for @usr_prod_id = 2 And @usr_prod_op_id = 0 )
prod_id product_name price lowest largest source_of_values
------- ------------- ------ ------ ------- --------------------
2 Product 2 2000 -200 200 Option - Direction
2 Product 2 2000 -150 0 Option - Smell
2 Product 2 2000 0 700 Option - Origin
/* R e s u l t : ( for @usr_prod_id = 0 And @usr_prod_op_id = 3 )
prod_id product_name price lowest largest source_of_values
------- ------------- ------ ------ ------- --------------------
1 Product 1 1000 100 900 Option - Grade
This solution reflects the way that I see the problem and possible solution, The code could be furtheremore adjusted and/or fine tuned if I got something wrong. What it can't do is to result with variable number of columns. The number of columns and their datatypes must be exact. If there is a need to get the result in some cases with 6 columns and in the other 4 (or 8) columns then there should be another SQL statement,