Write a query to display the manager name, department name, department phone number,…
- 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
- 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.