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
Advertisement
Answer
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.