• Skip to main content
  • Skip to primary sidebar
  • Skip to footer

Datannum

Data science and web development for small business applications

  • Home
  • Microsoft Excel (spreadsheet) forum
    • Project: Analyze NYSE Data
  • SQL forum
    • Project: Query a Digital Music Store Database
  • Data Visualization
    • Project: Data Visualization Project using Tableau
    • IBM – Cognos Analytics/Planning Analytics/Cognos Controller with Watson
  • Linear Algebra forum
    • Curated resource to learn linear algebra
  • Hire Us/Support

Self JOIN: Difficulty relating two copies of the same table

Data Science/Business Analytics for Small Business Applications › Forums › SQL › Self JOIN: Difficulty relating two copies of the same table

Tagged: SELF JOIN in SQL

  • This topic is empty.
Viewing 1 post (of 1 total)
  • Author
    Posts
  • April 23, 2022 at 3:54 am #142
    Admin
    Keymaster

      Here is the problem:

      All of the questions in this quiz refer to the open source Chinook Database. Please familiarize yourself with the ER diagram in order to familiarize yourself with the table and column names in order to write accurate queries and get the appropriate answers.

      Retrieve a list with the managers last name, and the last name of the employees who report to him or her.

      Here is my tentative solution:

      SELECT E.LastName, M.LastName AS ManagersLastName
      FROM Employees E
      JOIN Employees M 
      WHERE E.EmployeeId = M.ReportsTo
      
      

      After running the query described above*, who are the reports for the manager named Mitchell (select all that apply)?*

      Callahan

      Johnson

      Park

      Edwards

      King

      To my understanding, Mitchell reports to King and Callahan. In other words, Mitchell is the employee whose managers are King and Callahan.

      That however seems wrong as Mitchell should perhaps report to only one manager (if I am not wrong).

      So, the correct answer given multiple choices it appears should be Callahan and King. However, that defies my understanding and the way I proceeded.

      Any clue/help where I am going wrong appreciated. Thanks in advance!


      Reply


      You have your join condition around the wrong way, if you expand the aliases you can see why.

      SELECT Employee.LastName, Manager.LastName AS ManagersLastName
      FROM Employees Employee
      JOIN Employees Manager
      ON Employee.EmployeeId = Manager.ReportsTo
      

      Check the ON condition Employee.EmployeeId = Manager.ReportsTo, you want the EmployeeId of the manager which the Employee reports to e.g.

      SELECT Employee.LastName, Manager.LastName AS ManagersLastName
      FROM Employees Employee
      JOIN Employees Manager
      ON Employee.ReportsTo = Manager.EmployeeId
      

    • Author
      Posts
    Viewing 1 post (of 1 total)
    • You must be logged in to reply to this topic.
    Log In

    Primary Sidebar

    profile for Techcosec Limited on Stack Exchange, a network of free, community-driven Q&A sites

    RSS CS50’s Introduction to Computer Science by Harvard University on Edx

    • Are pairs array and lock array similar in structure after execution of sort_pairs function? Is my base case okay with pairs array or do I need to modify with referencing to lock array? May 27, 2023
    • Recursion in lock-pairs function while drawing analogy with sum of natural numbers: What will be the recursive case May 25, 2023
    • Understanding recursive function with Tideman May 24, 2023
    • More explanation for the Tideman project May 22, 2023
    • Why the below for loop not leading to segmentation fault or garbage value and instead giving correct output May 21, 2023

    Subscribe via Email

    Enter your email address to subscribe and receive notifications of new posts by email.

    This website hosted on WP Engine

    Footer

    Editor’s choice

    Developer jobs remote full time
    • Terms
    • Privacy
    • Disclaimer