MySQL Join Two Tables With Comma Separated Values

MySQL Join two tables with comma separated values

SELECT  a.nid,
        GROUP_CONCAT(b.name ORDER BY b.id) DepartmentName
FROM    Notes a
        INNER JOIN Positions b
            ON FIND_IN_SET(b.id, a.forDepts) > 0
GROUP   BY a.nid

• SQLFiddle Demo

MySQL join two tables to produce column with comma separated values

Try this query

SELECT tb.id,GROUP_CONCAT(ta.NumCol) AS NumCol FROM tableB AS tb
JOIN tableA AS ta ON ta.id=tb.id
GROUP BY tb.id

How to join two tables using a comma-separated-list in the join field

Using comma separated lists in a database field is an anti-pattern and should be avoided at all costs.

Because it is a PITA to extract those comma separated values out agian in SQL.

Instead you should add a separate link table to represent the relationship between categories and movies, like so:

Table categories
  id integer auto_increment primary key
  name varchar(255)

Table movies
  id integer auto_increment primary key
  name varchar(255)

Table movie_cat
  movie_id integer foreign key references movies.id
  cat_id integer foreign key references categories.id
  primary key (movie_id, cat_id)

Now you can do

SELECT m.name as movie_title, GROUP_CONCAT(c.name) AS categories FROM movies m
INNER JOIN movie_cat mc ON (mc.movie_id = m.id)
INNER JOIN categories c ON (c.id = mc.cat_id)
GROUP BY m.id

Back to your question

Alternativly using your data you can do

SELECT m.name as movie_title
  , CONCAT(c1.name, if(c2.name IS NULL,'',', '), ifnull(c2.name,'')) as categories 
FROM movies m
LEFT JOIN categories c2 ON 
 (replace(substring(substring_index(m.categories, ',', 2),
  length(substring_index(m.categories, ',', 2 - 1)) + 1), ',', '') = c2.id)
INNER JOIN categories c1 ON 
 (replace(substring(substring_index(m.categories, ',', 1), 
  length(substring_index(m.categories, ',', 1 - 1)) + 1), ',', '') = c1.id)

Note that the last query only works if there are 2 or fewer categories per movie.

mysql join two table with comma separated ids

You can use FIND_IN_SET() and GROUP_CONCAT() on this,

SELECT  b.Group_ID, GROUP_CONCAT(a.name) name
FROM    Table2 b
        INNER JOIN Table1 a
            ON FIND_IN_SET(a.ID, b.Group_ID) > 0
GROUP   BY b.Group_ID

• SQLFiddle Demo
• MySQL FIND_IN_SET
• MySQL GROUP_CONCAT()

OUTPUT

╔══════════╦═════════════════╗
║ GROUP_ID ║      NAME       ║
╠══════════╬═════════════════╣
║ 1        ║ Person1         ║
║ 2,3      ║ Person2,Person3 ║
╚══════════╩═════════════════╝

As a sidenote, this query might not perform efficiently as expected. Please do normalize your table properly by not saving values separated by a comma.

UPDATE

GROUP_ID is pretty much confusing. Isn't it PersonIDList? Anyway, here's my suggested schema design:

PERSON Table

• PersonID (PK)
• PersonName
• other columns..

GROUP Table

• GroupID (PK)
• GroupName
• other columns..

PERSON_GROUP Table

• PersonID (FK) (at the same time PK with column GroupID)
• GroupID (FK)

Join three tables with comma separated values in mysql

Join the tables and use group_concat():

select l.subject_id,
  group_concat(distinct s.system_desc order by find_in_set(s.system_id, l.system_csv_id)) system,
  group_concat(distinct c.class_desc order by find_in_set(c.class_id, l.class_csv_id)) class
from tbl_link l
inner join tbl_system s on find_in_set(s.system_id, l.system_csv_id) 
inner join tbl_class c on find_in_set(c.class_id, l.class_csv_id) 
group by l.subject_id

See the demo.

Results:

| subject_id | system                         | class                              |
| ---------- | ------------------------------ | ---------------------------------- |
| 1          | AMERICAN SYSTEM,BRITISH SYSTEM | GRADE 12,GRADE 8,GRADE 10,GRADE 11 |
| 2          | ANY,BRITISH SYSTEM             | GRADE 9,GRADE 10                   |

What's the difference between comma separated joins and join on syntax in MySQL?

There is no difference at all.

First representation makes query more readable and makes it look very clear as to which join corresponds to which condition.

---

Related Topics