How to conditionally select a field from joined tables in SQL?

user_13979505 0
Viewed 93
SELECT 
    title, 
    description, 
    status, 
    task.user_id AS creatorUserId, 
    (SELECT user.nickname FROM user WHERE user.id = task.user_id) AS creatorUserNickname,
    GROUP_CONCAT(user.id, "," ,user.nickname SEPARATOR ";") as responsibleUsers
FROM task JOIN task_user ON task.id = task_user.task_id
JOIN user ON task_user.user_id = user.id
GROUP BY title, description, status, creatorUserId, creatorUserNickname;

The query above works, but I wonder if it's possible to get the same result without a subquery.

I have a task table with user_id column for the user who created the task, and a user table with user data, like nickname.

Then there's a task_user table maping users assigned to the task.

My confusion lies on how to get different nicknames: one corresponding to task.user_id, the creator, and the others from task_user.user_id, the assignees.

The query below does't work, with creatorUserId and creatorUserNickname being repeated on responsibleUsers field, resulting in multiple rows for a single task.

SELECT 
    title, 
    description, 
    status, 
    task.user_id AS creatorUserId, 
    user.nickname AS creatorUserNickname,-- this user is referenced in task.user_id
    GROUP_CONCAT(user.id,"," ,user.nickname SEPARATOR ";") as responsibleUsers -- these in task_user.user_id
FROM task JOIN task_user ON task.id = task_user.task_id 
JOIN user ON task_user.user_id = user.id OR task.user_id = user.id
GROUP BY title, description, status, creatorUserId, creatorUserNickname;

Is there a way to select different user.nickname based on which table is referencing their id ?

Many thanks!

mysql sql
2 Answers

You need 2 joins to user.
The 1st to return the creator's details and the 2nd to return the users assigned to the task:

SELECT 
    t.title, 
    t.description, 
    t.status, 
    u1.user_id AS creatorUserId, 
    u1.nickname AS creatorUserNickname,
    GROUP_CONCAT(u2.id, ',' , u2.nickname SEPARATOR ';') AS responsibleUsers
FROM task t 
INNER JOIN task_user tu ON t.id = tu.task_id 
INNER JOIN user u1 ON t.user_id = u1.id 
INNER JOIN user u2 ON tu.user_id = u2.id 
GROUP BY t.title, t.description, t.status, creatorUserId, creatorUserNickname;
user_104988280

I would actually suggest that you write the query as:

SELECT t.title, t.description, t.status, 
       t.user_id as creatorUserId, u.nickname as creatorUserNickname,
       (SELECT GROUP_CONCAT(u.id, ',', u.nickname SEPARATOR ';)
        FROM task_users tu JOIN
             users u
             ON tu.user_id = u.user_id
        WHERE tu.task_id = t.id
       ) as responsibleUsers
FROM task t JOIN
     user u
     ON t.user_id = u.id ;

This avoids the outer aggregation -- which should improve performance. It also includes all tasks, even those with no responsible users. I'm guessing that is a good thing.

user_11440350
Related