0
SELECT
    cm.id,
    cg.cgm,
    m.dailyVal,
    m.workDaysYearly,
    cm.cId
FROM
    multiple_user AS cm
LEFT JOIN user_group cg ON(
    cm.community_master_name = cg.id
)
LEFT JOIN municipality m ON(cm.id = m.id)
WHERE
    cm.mun_id = 1

I have a above written query that produce the result as below

id   cgm                 dailyVal  workDaysYearly  cId
1   hello world grouping    46          260        147
1   hello world grouping    46          260        148
1   hello world grouping    46          260        149
1   h group                 46          260        150
1   h group                 46          260        151
1   h group                 46          260        152

Now I have a table 'master' where cId is stored in comma separated value in com_id which consist of data as follow:

id  mun_id com_id          total
1    1     147,148,149      150
2    1     150, 151, 152    33

and so on

I am looking for a result like below:

id   cgm  dailyVal  workDaysYearly  total
1   hello world grouping    46  260 150
1   h group                 46  260 33

How can I group by cgm and join table master to get that result.

sanin
  • 264
  • 1
  • 5
  • 18
  • It's best to avoid putting comma-separated lists in MySQL tables. – Barmar Jul 18 '17 at 07:26
  • 1
    Storing data as comma separated values voids first normal form. Though you can get the result using `Group_Concat` function. I suggest you to change the way data is stored in `Master` table. It will lead to complications in scenarios like this – Pரதீப் Jul 18 '17 at 07:33

0 Answers0