Combine Join query with Where condition query in SQL-CodePudding

I've two sql queries as below, 2 queries working alone fine.

SELECT * FROM
products
WHERE isLive IS FALSE AND
timeClose IS NULL AND
( timeOpen is NULL OR timeOpen <= 1 )
ORDER BY timeOpen ASC
LIMIT 11;

SELECT t1.* , GROUP_CONCAT('[','name:',t2.fileName,',','id:', t2.id,']') AS gallery
FROM products AS t1
JOIN gallery AS t2 ON t1.id=t2.productId
GROUP BY t1.id;

I tried below but have error

Error Code: 1140. In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'data_base.t1.id'; this is incompatible with sql_mode=only_full_group_by,

SELECT * FROM (
    
    SELECT t1.* , GROUP_CONCAT('[','name:',t2.fileName,',','id:', t2.id,']') AS gallery
    FROM products AS t1
    JOIN gallery AS t2 ON t1.id=t2.productId
) as t1
WHERE t1.isLive IS FALSE AND
t1.timeClose IS NULL AND
( t1.timeOpen is NULL OR t1.timeOpen <= 1 )
ORDER BY t1.timeOpen ASC
LIMIT 11

Please help me to find out the solution.. Thanks!

CodePudding user response：

As suggested

Error Code: 1140. In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'data_base.t1.id'; this is incompatible with sql_mode=only_full_group_by,

add GROUP BY t1.id to the subquery, which you missed from

SELECT t1.* , GROUP_CONCAT('[','name:',t2.fileName,',','id:', t2.id,']') AS gallery
FROM products AS t1
JOIN gallery AS t2 ON t1.id=t2.productId
**GROUP BY t1.id**;