Skip to content
TheChatArchiveThe Chat Archive
gpt-3.5-turbo-0613· openaiHugging Face Suzhen/CodeChat

Write a query to display the manager name, department name, department phone number,…

  1. User

    Write a query to display the manager name, department name, department phone number, employee name, customer name, invoice date, and invoice total for the department manager of the employee who made a sale to a customer whose last name is Hagan on May 18, 2015 (Figure P7.64). from lg table by using join function on my tsql but I don't have manager table at all

  2. Assistant

    Unfortunately, if you do not have a "manager" table, it will not be possible to directly retrieve the manager's name using a JOIN operation. However, you can still retrieve the other required information by joining the necessary tables.

    Assuming you have the following tables: "department", "employee", "customer", and "invoice" with appropriate columns, you can write a query as follows:

    SELECT e.name AS [Manager Name], 
           d.name AS [Department Name], 
           d.phone AS [Department Phone Number], 
           emp.name AS [Employee Name], 
           c.name AS [Customer Name], 
           i.invoice_date AS [Invoice Date], 
           i.total_amount AS [Invoice Total]
    FROM employee AS e
    JOIN department AS d ON e.department_id = d.department_id
    JOIN employee AS emp ON emp.department_id = d.department_id
    JOIN invoice AS i ON emp.employee_id = i.employee_id
    JOIN customer AS c ON i.customer_id = c.customer_id
    WHERE c.last_name = 'Hagan'
       AND i.invoice_date = '2015-05-18'
    

    Note that in the absence of a "manager" table, the "manager name" is presumed to be the name of the department's head, which is obtained by joining the "employee" table twice with department-based conditions.