Question 272666 in Mysql Question posted in
The official MySQL documentation can be found here.

MySql GROUP_CONCAT as Filter Query

I have two table X & Y:

Table X

id Value
1 "a,b,c,d"
2 "b,c"
3 "a,c,d"
4 "a,b,c"
5 "a,d"
6 "b,d"

Table Y

id Filter
1 "a,d"

And I want to get join table contain "a" & "d", result :

id Value
1 "a,b,c,d"
3 "a,c,d"
5 "a,d"

— I have tried this:

DROP TEMPORARY TABLE IF EXISTS `x`;
CREATE TEMPORARY TABLE `x` 
SELECT 1 as id, "a,b,c,d" as `value`
UNION SELECT 2 as id, "b,c" as `value`
UNION SELECT 3 as id, "a,c,d" as `value`
UNION SELECT 4 as id, "a,b,c" as `value`
UNION SELECT 5 as id, "a,d" as `value`
UNION SELECT 6 as id, "b,d" as `value`;
SELECT * FROM x;

DROP TEMPORARY TABLE IF EXISTS `y`; 
CREATE TEMPORARY TABLE `y` 
SELECT 1 as id, "a,d" as `filter`;
SELECT * FROM y;
 
SELECT x.id, x.`value`
FROM `x`
JOIN `y` ON x.`value` LIKE CONCAT('%', y.`filter`, '%');

— But the result only get id ‘5’, ‘a,d’.

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    Thank you Paul Maxwell, user2260040, ooxvyd for the answer. I will split table Y as a Row.

    DROP TEMPORARY TABLE IF EXISTS `x`;
CREATE TEMPORARY TABLE `x` 
SELECT 1 as id, "a,b,c,d" as `value`
UNION SELECT 2 as id, "b,c" as `value`
UNION SELECT 3 as id, "a,c,d" as `value`
UNION SELECT 4 as id, "a,b,c" as `value`
UNION SELECT 5 as id, "a,d" as `value`
UNION SELECT 6 as id, "b,d" as `value`;
SELECT * FROM x;

DROP TEMPORARY TABLE IF EXISTS `y`; 
CREATE TEMPORARY TABLE `y` 
SELECT 1 as id, "a" as `filter`
UNION SELECT 2 as id, "d" as `filter`;
SELECT * FROM y;

DROP TEMPORARY TABLE IF EXISTS `y2`; 
CREATE TEMPORARY TABLE `y2` 
SELECT * FROM `y`;

SELECT x.id, x.value
FROM x
WHERE (
  SELECT COUNT(*)
  FROM y
  WHERE FIND_IN_SET(y.filter, x.value)
) = (SELECT COUNT(*) FROM y2);

  2. 0

    You appear to wish to filter the rows in Table X depending on the values in Table Y. This may be accomplished by utilizing MySQL’s FIND_IN_SET function, which is handy for searching for a value in a comma-separated list.

    SELECT x.id, x.value
FROM x
WHERE (
  SELECT COUNT(*)
  FROM (
    SELECT DISTINCT value
    FROM x
    WHERE FIND_IN_SET(value, REPLACE(y.filter, ',', ','))
  ) AS filtered_values
) = (SELECT COUNT(DISTINCT value) FROM x WHERE x.id = 1);
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search