Intermediate Joins in SQL Step 5

I have issues with writing the query to solve the question 5

This is the question below

Write a query that returns information about each employee and their supervisor.

  • The report should include employees even if they do not report to another employee.
  • The report should be sorted alphabetically by the employee_name column.
  • Your query should return the following columns, in order:
    • employee_name - containing the first_name and last_name columns separated by a space, eg Luke Skywalker
    • employee_title - the title of that employee
    • supervisor_name - the first and last name of the person the employee reports to, in the same format as employee_name
    • supervisor_title - the title of the person the employee reports to
      I have attached a screenshot of my attempt to answer it and the error thrown!

Hi there! From what I understand about subqueries in SQL (I’m still learning as well!), a subquery creates a virtual table that you can use and reference just like an existing table. In your subquery, you have a virtual table aliased as sup, and it will have the following columns: supervisor_name and title. When you try to do a left join with sup and e1, there is no sup.employee_id column, so this is why it’s throwing up the error.

For this step of the mission, it’s not necessary to use a subquery for the recursive join. It helped me to think about it as 2 separate tables, or as “version 1” and “version 2”. It’s the same table, but we reference it with 2 different aliases as if they’re separate tables. On one side of the join (version1), your focus is on the employee information, and on the other side of the join (version2), your focus is on the supervisor information. It will look most similar to the first chunk of example code on the mission screen.

I hope this helps point you in the right direction.

1 Like