I have a query such as,
select name
from employee
inner join task on employee.id = task.employee_id
order by name asc
Tables look like:
employee
id name
1 Emily
2 Sam
3 AI
4 Joe
5 Daniel
6 John
task
task_id employee_id
A123 1
D456 3
A122 1
I believed the original query above would result in:
Al
Emily
But it is actually:
Al
Emily
Emily
I thought it would just return Emily once since inner join returns both values in both tables, but doesnt Emily appear only once in the employee table? I dont understand why it returns Emily twice even though it is listed twice in the task table?
Thanks
Emily has two tasks, hence her name record gets duplicated in the join, once for each match. I might use exists logic to get the result you want here:
SELECT e.name
FROM employee e
WHERE EXISTS (SELECT 1 FROM task t WHERE t.employee_id = e.id);
Read in plain English, the above query says to return any employee name (once) for which we can find at least one task in the task table.