Reference data that could or not exist in MySQL

Question

I have this tables:

courses: id_course,name_course
users: id_user,nickname_user
teachers: id_teacher,id_course_teacher,id_user_teacher (it has a value 
just in case this teacher is in the table users)

I'm listing one course with a list of all the teachers in it (with an auxiliary table):

SELECT c.name_course,group_concat(DISTINCT t.name_teacher) AS teachers 
FROM courses c
LEFT JOIN teachers t ON t.id_course_teacher=c.id_course
WHERE c.id_course='1';
It shows: Course 1: Teacher 1, Teacher 2, Teacher 5.

Some of those teachers have a profile in another table users and I want to show also the nickname of the teacher if it exists:

Course 1: Teacher 1, Teacher 2 (nickname), Teacher 5.

I tried this but I can not make a reference of the nickname to the teacher:

SELECT name_course,group_concat(DISTINCT t.name_teacher) AS teachers,
                   group_concat(DISTINCT u.nickname_user) AS nickname 
FROM courses c
LEFT JOIN teachers t ON t.id_course_teacher=c.id_course,
LEFT JOIN users u ON t.id_user_teacher=u.id_user 
WHERE c.id_course='1';
The value of nickname is just "nickname", but I can not reference it 
to the teacher 2.

What am I doing wrong? Should I make a subquery inside the LEFT JOINS? I am a bit lost with that. Thank you.

Solution by InoS Heo (full explaind in his comment)

I just had to add this:

GROUP_CONCAT(DISTINCT CONCAT(t.name_teacher, ':', IF(u.nickname_user IS NULL, 'NULL', u.nickname_user))) AS name_nick_map

So I have in $row['name_nick_map'] the information I want (teacher 1:null,teacher 2:nickname2,teacher5:null)

Answer 1

Here are your schema and data. http://www.sqlfiddle.com/#!2/7af94/1 We highly appreciate if you post your data in sqlfiddle.

I think to match teacher name and nickname, you should get rid of GROUP_CONCAT()

just like this:

SELECT name_course, t.name_teacher AS teachers,
                   u.nickname_user AS nickname 
FROM courses c
LEFT JOIN teachers t ON t.id_course_teacher=c.id_course
LEFT JOIN users u ON t.id_user_teacher=u.id_user 
WHERE c.id_course='c1';

+-------------+----------+-------------+
| name_course | teachers | nickname    |
+-------------+----------+-------------+
| c_name1     | t1_name  | u1_nickname |
| c_name1     | t2_name  | u2_nickname |
| c_name1     | t3_name  | NULL        |
+-------------+----------+-------------+

Assuming many to many relationship, I guess your db design is not NORMALIZED.

when teacher and course has many to many relationship, teacher_to_course table is required.

courses_to_teachers(id_course, id_teacher)

SELECT name_course, t.name_teacher AS teachers,
               u.nickname_user AS nickname 
FROM courses c
LEFT JOIN course_to_teachers ct ON c.id_course = ct.id_course
LEFT JOIN teachers t ON t.id_teacher = ct.id_teacher
LEFT JOIN users u ON t.id_user_teacher=u.id_user 
WHERE c.id_course='c1';

UPDATE

"I show the course with php, so could be possible to create an array array[t1_name=>u1_nickname, t2_name=>u2_nickname, etc...)?"

means, you are using GROUP_CONCAT() to make such array easier? I think it can be made without GROUP_CONCAT().

Anyway, if you need GROUP_CONCAT() why don't you try this? (you can test here http://www.sqlfiddle.com/#!2/7af94/24/0)

SELECT name_course, GROUP_CONCAT(CONCAT(t.name_teacher, ':', IF(u.nickname_user IS NULL, 'NULL', u.nickname_user))) AS name_nick_map
FROM courses c
LEFT JOIN teachers t ON t.id_course_teacher=c.id_course
LEFT JOIN users u ON t.id_user_teacher=u.id_user 
WHERE c.id_course='c1'
GROUP BY name_course;
+-------------+------------------------------------------------------+
| name_course | name_nick_map                                        |
+-------------+------------------------------------------------------+
| c_name1     | t1_name:u1_nickname,t2_name:u2_nickname,t3_name:NULL |
+-------------+------------------------------------------------------+

As you can see, "t3_name:NULL" represents "t3_name has no nickname"

making array

I wonder if you are asking this code. With following query and result.

SELECT name_course, t.name_teacher AS teacher, u.nickname_user AS nickname 
FROM courses c
LEFT JOIN teachers t ON t.id_course_teacher=c.id_course
LEFT JOIN users u ON t.id_user_teacher=u.id_user 
WHERE c.id_course='c1';
+-------------+----------+-------------+
| name_course | teacher  | nickname    |
+-------------+----------+-------------+
| c_name1     | t1_name  | u1_nickname |
| c_name1     | t2_name  | u2_nickname |
| c_name1     | t3_name  | NULL        |
+-------------+----------+-------------+

to make array.

$query = "SELECT ....";

$result = mysqli_query($query);

// error handling omitted

$CONTAINER = Array();
while ($row = mysqli_fetch_assoc($result))
{
    $CONTAINER[$row['teacher']] = $row['nickname']; 
}

print_r($CONTAINER);

mysqli_free_result($result);

Probably print_r($CONTAINER) will produce something like "array[t1_name=>u1_nickname, t2_name=>u2_nickname, etc...)"

Answer 2

You have some errors in your query, if you execute it right you get the teachers name grouped as you wished:

SELECT name_course,group_concat(DISTINCT t.name_teacher) AS teachers,
                   group_concat(DISTINCT u.nickname_user) AS nicknames
FROM courses c
LEFT JOIN teachers t ON t.id_course_teacher=c.id_course
LEFT JOIN users u ON t.id_user_teacher=u.id_user 
WHERE c.id_course='1';