Mysql - Compare int field with comma separated field from another table

Question

I have two tables in a MySQL database like this:

User:

userid     |userid      | Username     | Plan(VARCHAR) | Status  |
-----------+------------+--------------+---------------+---------+
1          | 1          |  John        | 1,2,3         |1        |
2          | 2          |  Cynthia     | 1,2           |1        |
3          | 3          |  Charles     | 2,3,4         |1        |

Plan: (planid is primary key)

planid(INT)  |  Plan_Name     | Cost     |  status      | 
-------------+----------------+----------+--------------+
1            |  Tamil Pack    |  100     |  ACTIVE      |
2            |  English Pack  |  100     |  ACTIVE      |
3            |  SportsPack    |  100     |  ACTIVE      |
4            |  KidsPack      |  100     |  ACTIVE      |

OUTPUT

id |userid | Username | Plan       | Planname                            |
---+-------+----------+------------+-------------------------------------+
1  | 1     |  John    | 1,2,3      |Tamil Pack,English Pack,SportsPack   |
2  | 2     |  Cynthia | 1,2        |Tamil Pack,English Pack              |
3  | 3     |  Charles | 2,3,4      |English Pack,Sportspack, Kidspack    |

Since plan id in Plan table is integer and the user can hold many plans, its stored as comma separated as varchar, so when i try with IN condition its not working.

SELECT * FROM plan WHERE find_in_set(plan_id,(select user.planid from user where user.userid=1))

This get me the 3 rows from plan table but i want the desired output as above.

How to do that.? any help Please

"Since plan id in Plan table is integer and the user can hold many plans, its stored as comma separated as varchar, so when i try with IN condition its not working. " If this is a new project you should stop the comma separted values madness and normalize the table structures and add the table `User_Plan` that holds the relations. — Raymond Nijland, Jul 07 '18 at 11:36
Please read my answer to this question: [Is storing a delimited list in a database column really that bad?](https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad/3653574#3653574) — Bill Karwin, Jul 07 '18 at 16:02

Raymond Nijland · Answer 1 · 2018-07-07T11:53:34.697

A rewrite off your query what should work is as follows..

Query

SELECT
   all columns you need
 , GROUP_CONCAT(Plan.Plan_Name ORDER BY Plan.planid) AS Planname
FROM
  Plan
WHERE
  FIND_IN_SET(Plan.plan_id,(
    SELECT
      User.Plan
    FROM
      user
    WHERE User.userid = 1
  )
)
GROUP BY
  all columns what are in the select (NOT the GROUP_CONCAT function)

You also can use FIND_IN_SET on the ON clause off a INNER JOIN. One problem is that the join won't ever use indexes.

Query

SELECT 
   all columns you need
 , GROUP_CONCAT(Plan.Plan_Name ORDER BY Plan.planid) AS Planname
FROM 
 User 
INNER JOIN 
 Plan
ON
 FIND_IN_SET(Plan.id, User.Plan)
WHERE 
 User.id = 1
GROUP BY
 all columns what are in the select (NOT the GROUP_CONCAT function)

Like i said in the comments you should normalize the table structures and add the table User_Plan whats holds the relations between the table User and Plan.

Mysql - Compare int field with comma separated field from another table

User:

Plan: (planid is primary key)

OUTPUT

1 Answers1