sqlmysqlsubquery

How do I access a field from the main query inside a sub query in mySQL?


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?


Solution

  • 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,