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
;
The cause of the problem was identified by @Phil in the comments:
Probably because it's nested too deep
You have 2 layers of nesting and the reference of table e
cannot "see" through these 2 layers in MySQL.
Correlated inline subquery can usually be converted to derived tables and then LEFT
joined to the other tables in the FROM
clause but they have to be turned into uncorrelated (in MySQL. In other DBMS, you could use a LATERAL
join or the similar OUTER APPLY
.
A first rewrite to get the job done:
SELECT
CONCAT_WS(
', ', count(DISTINCT CONCAT(q.emailaddress, '_', e.id)),
dv.OneTimeItems
) as AllItems,
e.id,
LEFT(e.firstname, 1) as voorletter,
e.lastname
FROM question q
LEFT JOIN employee e ON q.employee_id = e.id
LEFT JOIN
(
SELECT company_id,
GROUP_CONCAT(items SEPARATOR '; ') AS OneTimeItems
FROM (
SELECT oi.company_id,
CONCAT_WS(
': ', oi.item_name, SUM(oi.item_amount)
) items
FROM onetime_item oi
WHERE oi.date BETWEEN '2015-12-01'
AND LAST_DAY('2015-12-01')
GROUP BY oi.company_id, oi.item_name
) resulta
GROUP BY company_id
) AS dv
ON dv.company_id = e.company_id
WHERE 1=1
AND YEAR(q.created_at) = '2015'
AND MONTH(q.created_at) = '12'
GROUP BY e.company_id ;
Test in SQLfiddle.
Unrelated to the issue comments:
- There is
GROUP BY e.company_id
while the select
list has e.id, LEFT(e.firstname, 1), e.lastname
. All these will give arbitrary result from a (more or less random) employee for each company - or even in extremely rare cases arbitrary results from 2 or 3 different employees! MySQL allowed (before 5.7) such bad use of group by that could cause erroneous results. It has been fixed in 5.7 and the default settings would reject this query.
The condition:
YEAR(created_at) = '2015' AND MONTH(created_at) = '12'
cannot make use of indexes. It's better to rewrite with either BETWEEN
if the column is of DATE
type of with an inclusive-exclusive range condition, which works flawlessly with any datetime type (DATE
, DATETIME
, TIMESTAMP
) of any precision:
-- use only when the type is DATE only
date BETWEEN '2015-12-01' AND LAST_DAY('2015-12-01')
or:
-- use when the type is DATE, DATETIME or TIMESTAMP
created_at >= '2015-12-01' AND created_at < '2016-01-01'
Best Answer
Found a way to do this, though it feels like a hack and I hope somebody is able to find a different/more elegant way. The sessions variables are incremented once in WHERE and HAVING clauses, so I just added a conditional to see whether or not the column has been incremented yet. Now it's only getting incremented in the HAVING clause. Example of my hacky fix: http://sqlfiddle.com/#!9/cb25a4/35