I have a problem with my query and mysql throws the following error:
#1055 - Expression #66 of SELECT list is not in GROUP BY clause and
contains nonaggregated column 's.status' which is not functionally
dependent on columns in GROUP BY clause; this is incompatible with
IF(s.status, s.specials_new_products_price, null) as specials_new_products_price,
IF(s.status, s.specials_new_products_price, p.products_price) as final_price
FROM products p
LEFT JOIN specials s ON p.products_id = s.products_id
LEFT JOIN manufacturers m using(manufacturers_id) ,
WHERE p.products_view = 1
AND p.products_status = 1
AND p.products_archive = 0
AND c.virtual_categories = 0
AND p.products_id = pd.products_id
AND p.products_id = p2c.products_id
AND p2c.categories_id = c.categories_id
AND pd.language_id = 1
GROUP BY p.products_id;
When you use GROUP BY, you can use expressions in your select-list only if they have a single value per group. Otherwise you get ambiguous query results.
In your case, MySQL believes that
s.status might have multiple values per group. For example, you're grouping by
s.status is a column in another table
specials, perhaps in a one-to-many relationship with table
products. So there might be multiple rows in
specials with the same
products_id, but different values for
status. If that's the case, which value for
status should the query use? It's ambiguous.
In your data, you might happen to limit the rows such that you only have one row in
specials for each row in
products. But MySQL can't make that assumption.
MySQL 5.6 and earlier let you write such ambiguous queries, trusting that you know what you're doing. But MySQL 5.7 enables more strict enforcement by default (this can be made less strict to behave like earlier versions).
The fix is to follow this rule: Every column in your select-list must fall into one of three cases:
For more explanation read this excellent blog: Debunking GROUP BY myths
Re your comment, I can only make a guess because you have not posted your table definitions.
I'm guessing that
manufacturers are functionally dependent on
products, so it's okay to list them as is in the select-list. But this assumption may not be correct, I don't know your schema.
Anyway, the error about
s.status should be resolved by using an aggregate function. I'm using
MAX() as an example.
SELECT p.*, pd.*, m.*, MAX(IF(s.status, s.specials_new_products_price, NULL)) AS specials_new_products_price, MAX(IF(s.status, s.specials_new_products_price, p.products_price)) AS final_price FROM products p LEFT OUTER JOIN specials s ON p.products_id = s.products_id INNER JOIN manufacturers m ON p.manufacturers_id = m.manufacturers_id INNER JOIN products_description pd ON p.products_id = pd.products_id INNER JOIN products_to_categories p2c ON p.products_id = p2c.products_id INNER JOIN categories c ON p2c.categories_id = c.categories_id WHERE p.products_view = 1 AND p.products_status = 1 AND p.products_archive = 0 AND c.virtual_categories = 0 AND pd.language_id = 1 GROUP BY p.products_id;
I also rewrote your joins in the proper way. Comma-style joins should be avoided.