MYSQL: Find data where the condition have comma seperator

Question

I have table wla_user and table wla_container. I want to display data from container table if certain condition met from the table User.

Table wla_user

id	u_name	email	factory_id
1	Halimah	halimah@gmail.com	1,2

Table wla_container

id	contNUm	contStatus	factory_id
1	2122	Active	1
2	3312	Active	2
3	5123	Not Active	3
4	9817	ACtive	4

So if the user id = 1, The data that i want to look like is this:

id	contNUm	contStatus	factory_id
1	2122	Active	1
2	3312	Active	2

I try run this query. but then only show 1 data, which is factory 1. Suppose should be two data because the user factory is 1,2

SELECT id, contNUm, contStatus, factory_id 
FROM wla_container WHERE factory_id IN (SELECT factory_id FROM wla_user WHERE email = 'halimah@gmail.com')

Can anyone know how to fix this problem?

score 0 · Answer 1 · answered Apr 13 '23 at 06:47

You should avoid storing comma separated data in your tables. As a workaround, we can try using FIND_IN_SET() on the factory_id column in the wla_user table:

SELECT id, contNUm, contStatus, factory_id
FROM wla_container wc
WHERE EXISTS (
    SELECT 1
    FROM wla_user wu
    WHERE FIND_IN_SET(wc.factory_id, wu.factory_id) > 0
);

MYSQL: Find data where the condition have comma seperator

1 Answers1