Subquery and return as a chain

Question

Subquery and return as a chain

Navigation

#1 by (4 votes)

0

I am trying to have a subquery come as a broken string from a subquery.

This is the structure of the DB (I know it's not the way but when I got there I was and I need to get a job before normalizing)

STRUCTURE

CREATE TABLE 'TIENDAS' (
    'ID' INT(11) NOT NULL AUTO_INCREMENT,
    'TIENDA' VARCHAR(50) NULL DEFAULT NULL,
    'EMPLEADOS' VARCHAR(50) NULL DEFAULT NULL,
    PRIMARY KEY ('ID')
)
COLLATE='latin1_swedish_ci'
ENGINE=InnoDB;

CREATE TABLE 'EMPLEADOS' (
    'ID' INT(11) NOT NULL AUTO_INCREMENT,
    'NOMBRE' VARCHAR(50) NULL DEFAULT NULL,
    PRIMARY KEY ('ID')
)
COLLATE='latin1_swedish_ci'
ENGINE=InnoDB;

DATA

 EMPLEADOS
    ID | NOMBRE
    1    JUAN PEREZ
    2    PEDRO LOPEZ
    3    MARTIN GUZMAN
    4    OSCAR ACOSTA
    5    ALFONSO HITLER

    TIENDAS
    ID  |  TIENDA  | EMPLEADOS
    1     ACAPULCO   1,2,5
    2     SAN LUIS   4,1
    3     VALLARTA   3

I am trying to bring the names of each employee but I can not find the form and those return it as a string of names

$query = "SELECT T.ID, T.TIENDA
(SELECT E.NOMBRE FROM EMPLEADOS E WHERE E.ID IN ( T.EMPLEADOS )) AS EMPLEADOS
FROM TIENDAS T";

I hope an exit like this

ID | TIENDA   | EMPLEADOS
1    ACAPULCO   JUAN PEREZ, PEDRO LOPEZ, ALFONSO HITLER
2    SAN LUIS   OSCAR ACOSTA, JUAN PEREZ
3    VALLARTA   MARTIN GUZMAN

But I honestly do not know how to tell the DB that the subquery brings it as a string delimited by ","

Thanks

mysql

asked by Alberto Siurob 18.05.2018 в 22:49

source

1 answer

pass hidden variables and retrieve it in another page Move boostrap column to the right

score 4 · Accepted Answer

SELECT T.ID, T.TIENDA, 
(SELECT 
 GROUP_CONCAT(E.NOMBRE) 
 FROM EMPLEADOS E 
 WHERE FIND_IN_SET(E.ID, T.EMPLEADOS)
 ) AS EMPLEADOS
FROM TIENDAS T;

GROUP_CONCAT returns a string separated by commas from a select
FIND_IN_SET searches for the occurrence of a field in a string that is a comma separated list

In case the list of EMPLOYEES has spaces behind each comma (eg: '1, 2, 5' ) a REPLACE can be used, something like this:

WHERE FIND_IN_SET(E.ID, REPLACE(T.EMPLEADOS, ' ', ''))