MySQL temporary column in SELECT statement

MySQLmysql-5.1

I'd like to do some calculations in a MySQL query.
I've created some temporary columns which I'd like to use for the calculation.

SQL fiddle for currently working (but discouraged) SQL query:
http://sqlfiddle.com/#!2/5dc99/14

According to the MySQL docs 9.4 User-Defined variables(http://dev.mysql.com/doc/refman/5.0/en/user-variables.html): "As a general rule, other than in SET statements, you should never assign a value to a user variable and read the value within the same statement."

SELECT
  /** Product details **/
  product.id AS product_number,
  product.name AS product_name,

  /** Price details **/
  @redemption_price := product.redemption_price AS redemption_price,
  @customer_price_increment := (
    CASE
      (SELECT COUNT(1) FROM customer_price_increment AS cpi WHERE cpi.product_id = product.id AND cpi.customer_id = 4)
    WHEN 0 THEN
      0
    ELSE 
      @redemption_price * (SELECT cpi.increment_percentage / 100 FROM customer_price_increment AS cpi WHERE cpi.customer_id = 4 AND cpi.product_id = product.id)
    END
  ) AS customer_price_increment,
  @general_price_increment := (
    CASE
      (SELECT COUNT(1) FROM general_price_increment AS gpi WHERE gpi.product_id = product.id)
    WHEN 0 THEN
      0
    ELSE 
      @redemption_price * (SELECT gpi.increment_percentage / 100 FROM general_price_increment AS gpi WHERE gpi.product_id = product.id)
    END
    ) AS general_price_increment,
  @sale_price := @redemption_price + (
    CASE
      @customer_price_increment
    WHEN 0 THEN
      @general_price_increment
    ELSE
      @customer_price_increment
    END
  ) AS sale_price
FROM
  product

I'd like to add HAVING sale_price > 250 to only show products with a product price higher than $250.

Adding HAVING sale_price > 250 doesn't work, because of the usage of the user defined variables (see http://sqlfiddle.com/#!2/5dc99/19).

I've removed the user defined variables as much as I could, but I'm not getting it to work without all the user defined variables.
http://sqlfiddle.com/#!2/5dc99/24 shows results, because he HAVING clause isin't there.
http://sqlfiddle.com/#!2/5dc99/23 doesn't show any results while it should, because there are products that costs more than $250.

Simply removing all the user defined variables doesn't solve the problem, because the temporary columns cannot be reused (http://sqlfiddle.com/#!2/5dc99/25). Unknown column 'general_price_increment' in 'field list'.

SELECT
  /** Product details **/
  product.id AS product_number,
  product.name AS product_name,

  /** Price details **/
  product.redemption_price AS redemption_price,
  product.redemption_price * (
    CASE
      (SELECT COUNT(1) FROM customer_price_increment AS cpi WHERE cpi.product_id = product.id AND cpi.customer_id = 4)
    WHEN 0 THEN
      0
    ELSE 
      (SELECT cpi.increment_percentage / 100 FROM customer_price_increment AS cpi WHERE cpi.customer_id = 4 AND cpi.product_id = product.id)
    END
  ) AS customer_price_increment,
  product.redemption_price * (
    CASE
      (SELECT COUNT(1) FROM general_price_increment AS gpi WHERE gpi.product_id = product.id)
    WHEN 0 THEN
      0
    ELSE 
      (SELECT gpi.increment_percentage / 100 FROM general_price_increment AS gpi WHERE gpi.product_id = product.id)
    END
    ) AS general_price_increment,
  redemption_price + (
    CASE
      customer_price_increment
    WHEN 0 THEN
      general_price_increment
    ELSE
      customer_price_increment
    END
  ) AS sale_price
FROM
  product
HAVING
  sale_price > 250

What can I do to make this work?

Best Answer

Based on your current code, it appears that a product can have at most one match in either customer_price_increment or general_price_increment. With that fact in mind, I would probably try a different approach.

First, I would rewrite the base query returning all the prices like this:

SELECT
  p.id AS product_number,
  p.name AS product_name,
  p.redemption_price,
  COALESCE(p.redemption_price * cpi.increment_percentage / 100, 0) AS customer_price_increment,
  COALESCE(p.redemption_price * gpi.increment_percentage / 100, 0) AS general_price_increment,
  p.redemption_price * (1 + COALESCE(cpi.increment_percentage, gpi.increment_percentage, 0) / 100) AS sale_price
FROM product AS p
LEFT JOIN customer_price_increment AS cpi ON cpi.id = p.id AND cpi.customer_id = 4
LEFT JOIN general_price_increment AS gpi ON gpi.id = p.id

To filter on sale_price, I would just use the above query as a derived table, so that I could reference the sale_price alias and avoid repeating the entire expression in the WHERE clause:

SELECT *
FROM (
    SELECT
      p.id AS product_number,
      p.name AS product_name,
      p.redemption_price,
      COALESCE(p.redemption_price * cpi.increment_percentage / 100, 0) AS customer_price_increment,
      COALESCE(p.redemption_price * gpi.increment_percentage / 100, 0) AS general_price_increment,
      p.redemption_price * (1 + COALESCE(cpi.increment_percentage, gpi.increment_percentage, 0) / 100) AS sale_price
    FROM product AS p
    LEFT JOIN customer_price_increment AS cpi ON cpi.id = p.id AND cpi.customer_id = 4
    LEFT JOIN general_price_increment AS gpi ON gpi.id = p.id
) AS s
WHERE sale_price > 250
;

I believe you could also make the first query a view and then just select from it filtering the results as necessary:

SELECT *
FROM sale_prices_view
WHERE sale_price > 250
;
Related Question