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
;
Out of the box answer:
mysql> SELECT @@version REGEXP '^5.[6789]';
+------------------------------+
| @@version REGEXP '^5.[6789]' |
+------------------------------+
| 1 |
+------------------------------+
(Granted, it won't work forever.)
Best Answer
You are right it is a like a local table to the connection.
According to the link you have under the subheading
Temporary Tables
, it says:Simply put, once the DB Connection terminates (normally or abnormally), the table disappears.
Also, please be aware that only the DB Connection that created the temporary table can access it. If 10 different DB Connections perform
CREATE TEMPORARY TABLE
, each DB Connection would have its own table. No other DB Connection can access it.