Perform LIKE operation for each element of a GROUP_CONCAT in MySQL

user_15567152 0
Viewed 33

I have a subquery in Mysql that performs aggregation using the GROUP_CONCAT function for an attribute of the join table (1-N relationship)

SELECT t1.id, GROUP_CONCAT(t2.name) AS list 
FROM table1 t1 
LEFT JOIN table2 t2 ON t2.x_id = t1.id  
GROUP BY t1.id

that returns the following content:

id list
1 value-1,value_2,x-value-1
2 value-1
3 null

Now in the main query, I need to perform some kind of LIKE operation that maps through every single element of the set, and for example the following statement:

WHERE list LIKE "x-value%"

should return the element with the id 1, and its returning an empty result set.

Any ideas?

Response to Barmar answer:

I have not used the LIKE in the sub-query because I have a feature that can negative filter the names where I use FIND_IN_SET, that's why I am doing the group in the first place. For example if I didn't have done the grouping, would get the following result:

id value
1 value-1
1 value_2
1 x-value-1
2 value-1
3 null

And then if I didn't group it, and wanted the id's without the value value-1

WHERE value != "value-1"

it would return

id value
1 value_2
1 x-value-1

Meaning I would get the id 1, even if it has the value value-1

mysql sql
1 Answers

Use a regular expression to match x-value at either the beginning or after a comma.

WHERE list REGEX '(^|,)x-value'

But it would be better to do this in another subquery.

SELECT t1.id, GROUP_CONCAT(t2.name) AS list
FROM table1 AS t1
JOIN table2 AS t2 ON t1.id = t2.x_id
JOIN (
    SELECT DISTINCT x_id
    FROM table2
    WHERE name LIKE 'x-value%'
) ON t1.id = t2.x_id

There's no need to use LEFT JOIN since you don't need any results that have no match in table2 (since they won't match x-value%).

You need to join twice: Once to get all the names for each ID, and a second time to filter to just the IDs that have x-value% in any of the names.

user_14918950
Related