Search code examples
sqlleft-joininner-join

Selecting two names based on ID stored in different table

Suppose I have two tables:

  • Table 1: tasks with columns (id, authorId, assigneeId)

  • Table 2: employee with columns (id, name)

authorId and assigneeId in Table 1 are referenced to the id column in Table 2.

What is the way to select Task ID, Author Name and Assignee Name (Assignee name defaults to 'null' if no assigneeId is present) from these two tables?

I am not sure how to start, thus making it difficult. Could anyone give a hint on how to start the code?

Solution

  • You can join twice:

    select t.id, e1.name as author_name, e2.name as assignee_name
from tasks t
left join employees e1 on e1.id = t.author_id
left join employees e2 on e2.id = t.assignee_id