Search code examples
mysqlsubquery

How to use GROUP_CONACT and CONCAT in mysql sub query

I want to retrieve table records in single quotes with comma separated values and using this output as sub query inside main query to get records but my subquery is not working however writing separate two queries working fine, but I want to use single query instead of two queries.

Mysql query to get single quote values with comma separated:

SELECT GROUP_CONCAT(DISTINCT (CONCAT('''', date(created_on), '''' ))) as 
 strengths FROM table WHERE created_on BETWEEN '2020-08-01' AND '2020-08-04'

output:

'2020-08-01','2020-08-03'

Expected same output but using mysql subquery : (below query not retrieving records)

 SELECT * FROM `report` where created_on in (SELECT GROUP_CONCAT(DISTINCT 
   (CONCAT('''', date(created_on), '''' ))) FROM report WHERE created_on BETWEEN 
   '2020-08-01' AND '2020-08-04') order by created_on
Solution

  • If do formally, then

    SELECT * 
FROM report 
WHERE FIND_IN_SET( DATE(created_on), 
                   ( SELECT GROUP_CONCAT(DISTINCT DATE(created_on)) 
                     FROM report 
                     WHERE created_on BETWEEN '2020-08-01' AND '2020-08-04' 
                   )
                 ) 
ORDER BY created_on

    From the other side - subquery selects all dates in specifies dates range which are present in the table. Outer query selects rows which dates are present in this list, i.e. is present in the table. So ALL table rows within the range will be returned.