Inner join table with multiple rows/results grab the first one . Error : #1055

I have the following query :

SELECT p.product_id , p.product_slug , p.product_name , c.category_slug 
FROM products p 
INNER JOIN product_category_relations pcr ON pcr.relations_product_id = p.product_id 
INNER JOIN categories c ON c.category_id = pcr.relations_category_id 
GROUP BY p.product_id 
ORDER BY p.product_id 

Error:

#1055 – Expression #4 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘c.category_slug’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

One product can have multiple relations/categories INNER JOIN categories c ON c.category_id = pcr.relations_category_id, I only want to grab the first.

How can I achieve this with one query?

Example:

Answer

You need an aggregation function on the non-product columns, presumably group_concat():

SELECT p.product_id, p.product_slug, p.product_name,
       group_concat(c.category_slug) as category_slugs
FROM products p JOIN
     product_category_relations pcr
     ON pcr.relations_product_id = p.product_id JOIN
     categories c
     ON c.category_id = pcr.relations_category_id 
GROUP BY p.product_id 
ORDER BY p.product_id ;

In general, the SELECT columns must match the GROUP BY keys. In fact, that mismatch is causing your error.

In this query, the mismatch is allowed specifically because (presumably) product_id is the primary key of products. When you aggregate by the primary key (or any unique key), then you can select other columns. You can slo be explicit, though:

SELECT p.product_id, p.product_slug, p.product_name,
       group_concat(c.category_slug) as category_slugs
FROM products p JOIN
     product_category_relations pcr
     ON pcr.relations_product_id = p.product_id JOIN
     categories c
     ON c.category_id = pcr.relations_category_id 
GROUP BY p.product_id, p.product_slug, p.product_name 
ORDER BY p.product_id ;