How to Compare Comma Separated Ids on Left Join

How to compare comma separated ids on left join

table2 is outer joined but the condition table2.user_id IN (...) inside the where clause changes the query to an inner join. Move the condition from WHERE to ON clause:

select t1.name, t1.id, count(t2.id) as count
from table1 as t1
left join table2 as t2 on
  find_in_set(t1.id, t2.table1_ids) > 0 and
  t2.user_id in (1, 2)
group by t1.name, t1.id

SQL Fiddle

PS: WHERE 1 IN ('1,2') attempts to convert '1,2' to a number and thus matches 1.

How to compare comma separated ids on left join

table2 is outer joined but the condition table2.user_id IN (...) inside the where clause changes the query to an inner join. Move the condition from WHERE to ON clause:

select t1.name, t1.id, count(t2.id) as count
from table1 as t1
left join table2 as t2 on
  find_in_set(t1.id, t2.table1_ids) > 0 and
  t2.user_id in (1, 2)
group by t1.name, t1.id

SQL Fiddle

PS: WHERE 1 IN ('1,2') attempts to convert '1,2' to a number and thus matches 1.

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

Joining a table based on comma separated values

Maybe this uglyness, I have not checked results:

select names.name, courses.course_name
from names inner join courses
    on ',' + names.course_ids + ',' like '%,' + cast(courses.course_id as nvarchar(20)) + ',%'

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)

---

Related Topics