SQL JOIN query to get data with GROUP BY on one table only

Question

I am trying to fetch data from multiple table where i have one product with its details distributed in multiple tables. Here is one query i am using:

SELECT 
    fp.id, fp.name, fp.slug, fp.status, fp.approved, fp.created_at, fp.updated_at,
    fc.name as cat_name, fc.slug as category_slug, 
    ff.filepath,
    fdp.type, fdp.sku, fdp.regular_price, fdp.sale_price

FROM `foduu_products` fp 

JOIN `foduu_category_product` fcp 
ON fp.id = fcp.product_id

JOIN `foduu_filemanager` ff 
on fp.filemanager_id = ff.id 

JOIN `foduu_categories` fc 
on fcp.category_id = fc.id 

JOIN `foduu_details_product` fdp 
ON fp.id= fdp.product_id 

where fcp.category_id in (39,155,30,41,51)

group by fdp.product_id           <<<<--------------------  Here is the problem

ORDER by fp.id ASC

When i am using GROUP BY to get data from foduu_details_product where it hold multiple records for one product_id. When i am running this query i am getting this error.

#1055 - Expression #8 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'foduu-ecommerce-cms.fc.name' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

Not understanding why GROUP BY always give me these short of errors. Any suggestions how can i improve my query here?

Here is my database tables SQL for reference:

http://vbought.com/products%20table.sql

Thank you (in advance)!

You don't have any aggregate functions so group by is not appropriate see https://dev.mysql.com/doc/refman/8.0/en/group-by-handling.html for more details and why you are getting an error. — P.Salmon, May 11 '18 at 08:11
Which of the foduu_details_product would you want? Perhaps you could put foduu_details_product into a sub query rather than a direct join. — P.Salmon, May 11 '18 at 08:16
@user3201500 I don't think so, I guess the error target different attribute no? — Radim Bača, May 11 '18 at 08:17
Your version of mysql will not allow you to group by on 1 column only please read the link i sent you. — P.Salmon, May 11 '18 at 08:17
Is there any platform where i can show a demo of this database? Like there is jsfeddile, is there something for SQL as well? — user3201500, May 11 '18 at 08:21
sqlfiddle will do.But the group by seems to serve no purpose it would help if you could show your expected output. — P.Salmon, May 11 '18 at 08:21
@P.Salmon i have just attached a SQL table data. I am using MySQL 5.7 — user3201500, May 11 '18 at 08:36
A simple way to solve non aggregated columns: https://stackoverflow.com/a/40578865/439171 — Italo Borssatto, Mar 07 '20 at 18:34

score 0 · Answer 1 · answered May 11 '18 at 08:17

You don't have any aggregate function in your query so you should question the use of this specific statement in the first place.

You are in "sql_mode=only_full_group_by", thus you have to repeat every column in the select that is not use in an aggregate function (Max,Sum,Count...) in the group by statement.

SQL JOIN query to get data with GROUP BY on one table only

1 Answers1