A
You can get the results you want in one query by using one aggregation function.You don't indicate which database engine you're using. If you use MySQL, that function is https://dev.mysql.com/doc/refman/8.0/en/aggregate-functions.html#function_group-concat . If you use another different engine, you will have a similar feature at your disposal. Oracle for example has https://docs.oracle.com/cd/E11882_01/server.112/e41084/functions089.htm#SQLRF30030 . http://sqlfiddle.com/#!9/ea9bd8/1/0 to see how it works with test data, but I also hit them here below.CREATE TABLE CANDIDATOS (
DNI varchar(9) NOT NULL,
NOMBRE varchar(20) DEFAULT NULL,
APELLIDOS varchar(20) DEFAULT NULL,
SEXO char(1) DEFAULT NULL,
PRIMARY KEY (`DNI`)
);
CREATE TABLE IDIOMAS_ENCUESTA (
ID INT NOT NULL,
DNI_CANDIDATO varchar(9) NOT NULL,
ID_IDIOMA INT NOT NULL,
PRIMARY KEY (ID)
);
CREATE TABLE IDIOMAS (
ID INT NOT NULL,
DESCRIPCION varchar(20) DEFAULT NULL,
PRIMARY KEY (ID)
);
INSERT INTO CANDIDATOS (DNI, NOMBRE, APELLIDOS, SEXO) VALUES ('1','RICARDO','RUBIO','V');
INSERT INTO CANDIDATOS (DNI, NOMBRE, APELLIDOS, SEXO) VALUES ('2','JUAN','TRUJILLO','V');
INSERT INTO IDIOMAS (ID, DESCRIPCION) VALUE (1,'INGLES');
INSERT INTO IDIOMAS (ID, DESCRIPCION) VALUE (2,'FRANCES');
INSERT INTO IDIOMAS (ID, DESCRIPCION) VALUE (3,'ITALIANO');
INSERT INTO IDIOMAS (ID, DESCRIPCION) VALUE (4,'ALEMAN');
INSERT INTO IDIOMAS_ENCUESTA (ID, DNI_CANDIDATO, ID_IDIOMA) VALUE (1,'1',1);
INSERT INTO IDIOMAS_ENCUESTA (ID, DNI_CANDIDATO, ID_IDIOMA) VALUE (2,'1',2);
INSERT INTO IDIOMAS_ENCUESTA (ID, DNI_CANDIDATO, ID_IDIOMA) VALUE (3,'2',2);
INSERT INTO IDIOMAS_ENCUESTA (ID, DNI_CANDIDATO, ID_IDIOMA) VALUE (4,'2',3);
INSERT INTO IDIOMAS_ENCUESTA (ID, DNI_CANDIDATO, ID_IDIOMA) VALUE (5,'2',4);
Modifying a bit of the consultation you include, we get the expected resultSELECT C.DNI,
C.NOMBRE,
C.APELLIDOS,
C.SEXO,
GROUP_CONCAT(ID.DESCRIPCION SEPARATOR ',') AS LISTA_IDIOMAS
FROM candidatos c
inner join idiomas_encuesta i on c.dni = i.dni_candidato
inner join idiomas id on i.id_idioma = id.id
GROUP BY C.DNI, C.NOMBRE, C.APELLIDOS, C.SEXO;
Result with my reduced data gameDNI NOMBRE APELLIDOS SEXO LISTA_IDIOMAS
1 RICARDO RUBIO V FRANCES,INGLES
2 JUAN TRUJILLO V ALEMAN,ITALIANO,FRANCES
Next time try to add to your question the structure of the tables (CREATE) and some test data game (INSERT) so that whoever wants to help you can copy them and add them to your answer. You are always in time to edit and include this information.BONUSIf you want the language list to be sorted out, you have to apply an ORDER before applying the aggregation. Here I put another example query with the data sorted otherwise.SELECT APELLIDOS,
NOMBRE,
DNI,
SEXO,
GROUP_CONCAT(IDIOMA SEPARATOR ',') AS LISTA_IDIOMAS
FROM (
SELECT C.APELLIDOS AS APELLIDOS,
C.NOMBRE AS NOMBRE,
C.DNI AS DNI,
C.SEXO AS SEXO,
ID.DESCRIPCION AS IDIOMA
FROM candidatos c
inner join idiomas_encuesta i on c.dni = i.dni_candidato
inner join idiomas id on i.id_idioma = id.id
ORDER BY IDIOMA, C.APELLIDOS, C.NOMBRE
) AS A
GROUP BY APELLIDOS, NOMBRE, DNI, SEXO;
Outcome bonusAPELLIDOS NOMBRE DNI SEXO LISTA_IDIOMAS
RUBIO RICARDO 1 V INGLES,FRANCES
TRUJILLO JUAN 2 V ITALIANO,FRANCES,ALEMAN