JOINS with chains delimited by commas

Question

JOINS with chains delimited by commas

Navigation

#1 by (3 votes)
#2 by (2 votes)

3

I have a table called USERS, which has an index of certain nationalities as a string delimited by commas.

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 the INNER JOIN make me bring the names? I tried:

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

I hope an exit as well as

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 obtain 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.

Thanks

mysql sql php

asked by Alberto Siurob 02.07.2018 в 22:01

source

2 answers

2

If it's Mysql , you can use

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

If it is SQLServer 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;

answered by 02.07.2018 в 23:13

Problem does not work setOnChildClickListener in ExpandableListView how can I increase the degrade android

score 3 · Accepted 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, you can achieve what you want by 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

Notice that I have applied a ORDER BY within the GROUP_CONCAT to give me the list of ordered nations. If you are interested in any order you can remove it.

Demo

Here you can see a DEMO EN REXTESTER .