Rosamunda Rosamunda - 4 months ago 8
MySQL Question

MYSQL SELECT query: How to concatenate 4 tables with AND and OR conditions?

I have four MySQL tables:

usuarios (have the list of all users and their personal information)

roles (have the list of available roles in the site, that determines their permissions)

rolesUsuarios (links each user with their roles. They can have more than one)

cursos (have the list of courses of the site with each course description)

cursosUsuarios (links each user with all the courses where they are enrolled)

I want to make a SELECT query that shows me for a specific user, all their personal information, the roles he has and the courses where he's enrolled.

I've created this query that does work:

SELECT usuarios.userID, usuarios.userEmail, usuarios.userApellido, usuarios.userNombres,
usuarios.userDNI, usuarios.userFechaGeneracion,
rolesUsuarios.userID, rolesUsuarios.nombreRol,
cursosUsuarios.userID, cursosUsuarios.cursoID,
cursos.cursoID, cursos.nombreCurso, cursos.cursoYear, cursos.cursoMes,
cursos.modalidadCurso,
GROUP_CONCAT(cursos.nombreCurso, ':', cursosUsuarios.cursoID ORDER BY cursosUsuarios.cursoID SEPARATOR ',') AS 'cursos'
FROM usuarios JOIN rolesUsuarios JOIN cursosUsuarios JOIN cursos
WHERE usuarios.userID='10'
AND rolesUsuarios.userID = usuarios.userID
AND cursosUsuarios.userID = usuarios.userID
AND cursosUsuarios.cursoID = cursos.cursoID
GROUP BY usuarios.userID


The problem is that if the user isn't enrolled in any course, it won't show any result. How may I do that?

These are each tables structure:

CREATE TABLE roles(
roleID int unsigned not null auto_increment primary key,
nombreRol char(50) not null
);

CREATE TABLE usuarios(
userID int unsigned not null auto_increment primary key,
userEmail char(50) null,
userApellido char(50) null,
userNombres char(50) null,
userDNI int(10) null,
userPass char(65) null,
userFechaGeneracion char(10) null
);

CREATE TABLE rolesUsuarios (
rolesUsuariosID int unsigned not null auto_increment primary key,
userID int not null,
nombreRol char(50) not null
);

CREATE TABLE cursos (
cursoID int unsigned not null auto_increment primary key,
nombreCurso char(100) not null,
cursoYear int(4) not null,
cursoMes char(3) not null,
cursoFechaInicio char(10) null,
modalidadCurso char(50) not null
);

CREATE TABLE cursosUsuarios (
cursosUsuariosID int unsigned not null auto_increment primary key,
userID int not null,
cursoID int not null
);

Answer

I think something like this will work:

SELECT  usuarios.userID, usuarios.userEmail, usuarios.userApellido, usuarios.userNombres, 
                usuarios.userDNI, usuarios.userFechaGeneracion,
                rolesUsuarios.userID, rolesUsuarios.nombreRol,
                cursosUsuarios.userID, cursosUsuarios.cursoID, 
                cursos.cursoID, cursos.nombreCurso, cursos.cursoYear, cursos.cursoMes, 
                cursos.modalidadCurso,
GROUP_CONCAT(cursos.nombreCurso, ':', cursosUsuarios.cursoID ORDER BY cursosUsuarios.cursoID SEPARATOR ',') AS 'cursos'
FROM usuarios LEFT JOIN rolesUsuarios
    ON usuarios.userID = rolesUsuarios.userID
LEFT JOIN cursosUsuarios 
    ON usuarios.userID = cursosUsuarios.userID
LEFT JOIN cursos
    ON cursosUsuarios.cursoID = cursos.cursoID
WHERE usuarios.userID='10'
GROUP BY usuarios.userID;

Also, if you are selecting a single userID in the WHERE clause there is no need for the GROUP BY userID line.