how to match two different columns in Mysql which has comma separated values

Question

I have two tables:

CampaignTable

which has following property

id , campaign ,user_group

example would be

1  8867116213  5,11,15,16,18,20
2  8867116214  0,8,22

Then I have another table called User Table

with following property

id emp_id  user_group

Example is like this

1  274  0,5,8,9,10,11,21,20
2  275  5,11,20
3  279  19,21,22,25

I have to join this table and create an Array which has campaign wise user

for example for campaign with id 1 it should give me

274, 275

How can I achieve this in Mysql

Thanks

As @IvankaTodorova says, normalize your database. Proceeding with this design is going to make life extremely difficult. — elixenide, Oct 16 '16 at 18:54
Can you give me some idea How would I normalise? I mean which columns I should do — Vikram Anand Bhushan, Oct 16 '16 at 18:57
@IvankaTodorova can you help me to do that , WHat all columns should I normalise ? — Vikram Anand Bhushan, Oct 16 '16 at 19:08
see [Junction Tables](http://stackoverflow.com/a/32620163) . If you ever code like the above, I would never showcase it in an interview — Drew, Oct 16 '16 at 20:12

score 3 · Accepted Answer · answered Oct 16 '16 at 19:12

You should definetely normalize your data. For example consider this kind of normalization which renders almost no change to your DB structure:

INSERT INTO CampaignTable
    (`campaign`, `user_group`)
VALUES
    (8867116213, 5),
    (8867116213, 11),
    (8867116213, 15),
    (8867116213, 16),
    (8867116213, 18),
    (8867116213, 20),
    (8867116214, 0),
    (8867116214, 8),
    (8867116214, 22)
;

INSERT INTO UserTable
    (`emp_id`, `user_group`)
VALUES
    (274, 0),
    (274, 5),
    (274, 8),
    (274, 9),
    (274, 10),
    (274, 11),
    (274, 21),
    (274, 20),
    (275, 5),
    (275, 11),
    (275, 20),
    (279, 19),
    (279, 21),
    (279, 22),
    (279, 25)
;

You could then fetch your data with a query as simple as that:

SELECT c.campaign, GROUP_CONCAT(DISTINCT u.emp_id) FROM CampaignTable c 
JOIN UserTable u ON c.user_group = u.user_group
GROUP BY c.campaign

See SQLFiddle

how to match two different columns in Mysql which has comma separated values

1 Answers1