wildthing wildthing - 4 months ago 10
SQL Question

SQL default values in many-to-many relationship

I have the following many-to-many relationship:

user task user_task

id| name id| name u_id| t_id
--------- ----------- ----------
1 | john 1 | default 1 | 2
2 | anna 2 | task2


Now I want to assign the default task to all users who are not assigned to a task.

I got the following query that finds all users with no task:

SELECT u.id, ut.t_id FROM user u LEFT JOIN user_task ut ON (u.id = ut.u_id) LEFT JOIN task t ON (ut.t_id = t.id) WHERE ut.t_id is null;


And this query gives me the id of the default task:

SELECT id FROM task WHERE name='default';


But how can I combine them to insert the default task into the
user_task
table? The result should be:

user task user_task

id| name id| name u_id| t_id
--------- ----------- ----------
1 | john 1 | default 1 | 2
2 | anna 2 | task2 2 | 1

Answer

You can try this INSERT .. SELECT and cross join to the default row in task table and then filter only users that doesn't appear is user_task :

INSERT INTO user_task
SELECT u.id,t.id
FROM user u
CROSS JOIN task t
LEFT JOIN user_task ut
 ON(ut.u_id = u.id)
WHERE t.name = 'default'
  AND ut.u_id is null
Comments