Home / Questions / 177378
Accepted
Alberto Siurob
Asked: 2020-07-03 12:01:30 +0800 CST 2020-07-03 12:01:30 +0800 CST

JOINS with comma delimited strings

  • 772

I have a table called USERS which indexes certain nationalities as a comma delimited string.

ID | NOMBRE | NACIONALIDAD
1  | JUAN   | 1,2,3
2  | PEDRO  | 1,2
3  | JOSE   | 1,3

I have my table of NATIONALITIES

ID | NACION
1  | Ruso
2  | Español
3  | Brasileño

How could I make the INNER JOIN so that it brings me the names? I tried with:

SELECT U.* FROM USERS U INNER JOIN NACIONALIDADES N ON U.NACIONALIDAD IN ( N.ID )

I expect such an output

ID | NOMBRE | NACIONALIDAD
1  | JUAN   | Ruso,Español,Brasileño
2  | PEDRO  | Ruso,Español
3  | JOSE   | Ruso,Brasileño

I am using PHP, I could first get an initial array of nationalities and then compare the indexes, but I would like to know if there is a direct query with the database.

Thank you

php

2 Answers

  1. Best Answer

    We agree that it will be necessary to normalize your situation.

    However your question seemed curious to me, so I tried a solution. In fact, what you want can be achieved using FIND_IN_SET.

    The query would be like this:

    SELECT 
    u.user_id, 
    GROUP_CONCAT(n.nacion ORDER BY n.nacion) naciones  
FROM users_20180702 u 
INNER JOIN nacionalidades_20180702 n 
    ON FIND_IN_SET(n.nacion_id,u.nacionalidad)
GROUP   BY u.user_id;

    And the result:

    user_id       nombre        naciones
-------------------------------------
1             JUAN          BR,ES,RU
2             PEDRO         ES,RU
3             JOSÉ          BR,RU

    Note that I have applied an ORDER BYinside GROUP_CONCATto give me the list of ordered nations. If you are interested in any order you can remove it.

    show

    Here you can see a DEMO EN REXTESTER.

    • 4

  2. If it is Mysql, you can use

    SELECT id, GROUP_CONCAT(string SEPARATOR ' ') FROM table GROUP BY id;

    Yes it isSQLServer XML PATH

    SELECT
     [User], Activity,
     STUFF(
         (SELECT DISTINCT ',' + PageURL
          FROM TableName
          WHERE [User] = a.[User] AND Activity = a.Activity
          FOR XML PATH (''))
          , 1, 1, '')  AS URLList
FROM TableName AS a
GROUP BY [User], Activity

    Postgres also has one

    SELECT company_id, string_agg(employee, ', ')
FROM mytable
GROUP BY company_id;
    • 2