SQL Table Help

employee_id employee_name min_desired_salary
1 John 45000
2 Jane 55000
3 Matt 100000
4 Andy 75000
5 Amanda 90000
employee_id manager_employee_id
1 2
2 3
4 3
5 3
employee_id salary
1 56000
2 67500
3 110000
4 70000
5 90000
Write a query to retrieve the following requests:
1) List of employees, their manager's name (or 'NONE' if no manager)
2) List of employees, True False if they are manager or not
3) List of employees, True False if they are happy with their salary
4) List of managers and the number of happy employees they have
- Ordered by # of happy employees in descending order

Not sure if this is needed as well:

MANAGER_EMPLOYEES.employee_id -> EMPLOYEES.employee_id
MANAGER_EMPLOYEES.manager_employee_id -> EMPLOYEES.employee_id
EMPLOYEE_SALARAIES.employee_id -> EMPLOYEES.employee_id

Sure looks like some type of homework or assignment, so I won't give full answers.

But you do need to use LEFT JOIN instead of INNER JOIN for most of these. #4 will require a GROUPing of some type, the others not.

1 Like

It is an assignment for a summer class and all resources (and answers) are welcome!

I got this for number 1, but it isn't showing the manager name:

SELECT employees.employee_name
FROM employees
left join manager_employees
on employees.employee_id = manager_employees.employee_id;

You need to explicitly list every column you want to see, something like this:

SELECT e.employee_name, ISNULL(me.manager_employee_id, 'NONE') AS manager_employee_id
FROM employees e
left join manager_employees me
on e.employee_id = me.employee_id;

If you need to change the mgr's id to a name, you'll have to look it up back in employees ... that's where the alias I added to the table names is critical, because it allows you to reference the same table twice in the same query and be able to tell the columns from each reference apart.