Skip to content
Advertisement

Inner Join returned values

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.

User contributions licensed under: CC BY-SA
3 People found this is helpful
Advertisement