Could anyone help with an SQL question?

[gaac510]

I was required to solve this question for a pre-interview test. On the right was the query I wrote.

One of the tests didn’t pass. Apparently it’s an edge case for which my query wasn’t working.

Could anyone help with figuring out what that failed edge case may look like?

[RandellDawson]

You are not accounting for the case where there is no assignee for a task. Your current query will only capture tasks where there is an assignee id attached to the task.

[gaac510]

Thank you, but I did LEFT JOIN, so any records in tasks where assigneeId is null should still be selected.

I managed to figure out the problem. It was that I didn’t SELECT DISTINCT when producing the derived table, which means if multiple tasks were assigned to the same employee, the derived table would have identical rows, and, when joined with table tasks, those tasks with the same assigneeId would be joined with duplicate rows.

Or, I could have gone with an even simpler solution:

SELECT t.taskId, e1.name, e2.name
FROM tasks t
JOIN employees e1 ON t.authorId = e1.id
LEFT JOIN employees e2 ON t.assigneeId = e2.id;

[Sibert]

@gaac510

JOIN employees e1 ON t.authorId = e1.id
LEFT JOIN employees e2 ON t.assigneeId = e2.id;

Do you have to join a table twice? Try to use WHERE instead.

WHERE t.assigneeId = e2.id;