CODEWITHSUNDEEP
mysql
Subhash kumar
Subhash kumar

Reputation: 11

Mysql query for comma separated field values

I have a table that contains as comma separated values in three fields the primary keys of three different tables, for example:

Table A (Main table):
id   title   companyid   countryid   regulatorid
1    tit1     1,2         2,3         2,1
2    tit2     3,1         1,2         1,3
Table B (company)          table c (Country)       table d(regulator)
id   title                    id    title          id      title
1    comp1                     1      country1      1        regul1
2    comp2                     2      country2       2       regulator2
3    comp3                     3      country3      3       regulator

I want to get a result like:

id        title       company        country                   regulator
1         tit1        comp1,comp2    country2,counrtry3      regulator2,regul1
2        tit2         comp3,comp1    country1,country2        regul1,regulator3

I have made query like: 

   select id,title,group_concat(table B.title) AS company,group_concat(table c.title) AS 
country,group_concat(table d.title) AS regulator from table A INNER JOIN table b on 
find_in_set(table B.id,table A.companyid) > 0 INNER JOIN table c on find_in_set(table 
c.id,table A.countryid) > 0 INNER JOIN table d on find_in_set(table d.id,table 
A.regulatorid) > 0

What do I have to change to get the current result?

Upvotes: 0

Views: 784

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271003

This is such a horrible data format that before answering, I should make you promise to change the format if you have control over it. This should really be three additional separate association/junction tables. If you are going to use a relational database, use it right.

select a.id, a.title, group_concat(distinct b.title), group_concat(distinct c.title),
       group_concat(distinct d.title)
from a left outer join
     b
     on find_in_set(b.id, a.companyid) > 0 left outer join
     c
     on find_in_set(c.id, a.countryid) > 0 left outer join
     d
     on find_in_set(d.id, a.regulatorid) > 0
group by a.id, a.title;

But it would be much much much better to change the table layout.

Upvotes: 1

Related Questions