CODEWITHSUNDEEP
phpmysqllaravel-4
Vikram Anand Bhushan
Vikram Anand Bhushan

Reputation: 4896

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

I have two tables:

  1. 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

Upvotes: 0

Views: 148

Answers (1)

Te Ko
Te Ko

Reputation: 768

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

Upvotes: 3

Related Questions