mhk mhk - 6 months ago 16
SQL Question

Duplicate data in LEFT Join mysql query

may be a duplicate question...
here are two tables tasks_0_tasks_staff and tasks_0_staff_time_management:


tasks_0_tasks_staff

+----+---------+---------+
| id | task_id | user_id |
+----+---------+---------+
| 1 | 1 | 348 |
| 2 | 1 | 350 |
| 3 | 2 | 350 |
| 4 | 2 | 351 |
| 5 | 2 | 357 |
+----+---------+---------+


and


tasks_0_staff_time_management
+----+---------+---------+-------------+-------+
| id | user_id | task_id | day_of_week | hours |
+----+---------+---------+-------------+-------+
| 1 | 350 | 1 | 2 | 2 |
| 2 | 350 | 1 | 3 | 3 |
+----+---------+---------+-------------+-------+


when i execute following query it shows duplicate data in left join table... how can i show null (which is the actual case)...


SELECT tasks_0_tasks_staff.`task_id`,
tasks_0_staff_time_management.day_of_week,
tasks_0_staff_time_management.hours

FROM tasks_0_tasks_staff

LEFT JOIN `tasks_0_staff_time_management` ON `tasks_0_staff_time_management`.`user_id`= `tasks_0_tasks_staff`.`user_id`

WHERE `tasks_0_tasks_staff`.`user_id`= 350

ORDER BY `tasks_0_tasks_staff`.`task_id` ;

results:
+---------+-------------+-------+
| task_id | day_of_week | hours |
+---------+-------------+-------+
| 1 | 2 | 2 |
| 1 | 3 | 3 |
| 2 | 2 | 2 |
| 2 | 3 | 3 |
+---------+-------------+-------+


why the last two rows in result is showing day_of_week and hours column values as these don't exists in left join table? how can these two rows shows NULL value... ?

i'm expecting the results to be like this


+---------+-------------+-------+
| task_id | day_of_week | hours |
+---------+-------------+-------+
| 1 | 2 | 2 |
| 1 | 3 | 3 |
| 2 | NULL | NULL |
| 2 | NULL | NULL |
+---------+-------------+-------+

Answer

You should add task_id as a condition on your join:

SELECT tasks_0_tasks_staff.`task_id`,
    tasks_0_staff_time_management.day_of_week,
    tasks_0_staff_time_management.hours 
FROM tasks_0_tasks_staff 
LEFT JOIN `tasks_0_staff_time_management` ON `tasks_0_staff_time_management`.`user_id`= `tasks_0_tasks_staff`.`user_id`  AND `tasks_0_staff_time_management`.`task_id`= `tasks_0_tasks_staff`.`task_id`
WHERE `tasks_0_tasks_staff`.`user_id`= 350 
ORDER BY `tasks_0_tasks_staff`.`task_id` ;

Or with some more semantic SQL:

SELECT tasks_0_tasks_staff.`task_id`,
    tasks_0_staff_time_management.day_of_week,
    tasks_0_staff_time_management.hours 
FROM tasks_0_tasks_staff 
LEFT JOIN `tasks_0_staff_time_management` USING (`task_id`, `user_id`)
WHERE `tasks_0_tasks_staff`.`user_id`= 350 
ORDER BY `tasks_0_tasks_staff`.`task_id` ;

Here's a little proof of concept SQL fiddle:

http://sqlfiddle.com/#!9/2a2425/1

Comments