CODEWITHSUNDEEP
mysqlsqldatabaseselectgroup-by
geethuth
geethuth

Reputation: 103

mysql query for retrieving multiple values as array

I have two tables user and task as follows:

user:

enter image description here

task:

enter image description here

I need to query to get all the user ids of the task id whose user is 2 and the output table should be: enter image description here

Can anybody help me with the query? Thanks in advance.

Upvotes: 1

Views: 1175

Answers (1)

id'7238
id'7238

Reputation: 2603

Use the GROUP_CONCAT function:

SELECT 
    t.task_id AS task_id,
    GROUP_CONCAT(t.user_id) AS user_id,
    GROUP_CONCAT(u.username) AS user
FROM task t
JOIN user u ON t.user_id = u.id
WHERE EXISTS (
   SELECT 1 FROM task
   WHERE task.task_id = t.task_id AND task.user_id = 2
)
GROUP BY t.task_id

Upvotes: 2

Related Questions