Операторы LEFT OUTER JOIN и RIGHT OUTER JOIN. Задача

Question

Операторы LEFT OUTER JOIN и RIGHT OUTER JOIN. Задача

Руслан Назаров Ученик (112), на голосовании 3 месяца назад

Выгрузи таблицу из двух полей: первое поле с фамилией сотрудника, второе — с количеством пользователей, чьи запросы этот сотрудник обработал. Назови поля employee_last_name и all_customers соответственно. Сгруппируй записи по идентификатору сотрудника. Отсортируй количество пользователей по убыванию.
SELECT e.last_name AS employee_last_name,
COUNT(c.customer_id) AS all_customers
FROM employee AS e
INNER JOIN customer AS c ON e.employee_id = c.support _rep_id
GROUP BY e.employee_id, e.last_name
ORDER BY all_customers DESC;

- не работает!

SELECT s.last_name AS employee_last_name,
COUNT(c.customer_id) AS all_customers
FROM staff AS s
LEFT OUTER JOIN client AS c ON s.employee_id = c.support _rep_id
GROUP BY s.employee_id
ORDER BY all_customers DESC, employee_last_name;

-не работает

Answer 1

 SELECT e.last_name AS employee_last_name,  
       COUNT(c.customer_id) AS all_customers  
FROM employee AS e  
LEFT OUTER JOIN customer AS c ON e.employee_id = c.support_rep_id  
GROUP BY e.employee_id, e.last_name  
ORDER BY all_customers DESC, employee_last_name;

 SELECT e.last_name AS employee_last_name,  
       COUNT(c.customer_id) AS all_customers  
FROM employee AS e  
RIGHT OUTER JOIN customer AS c ON e.employee_id = c.support_rep_id  
GROUP BY e.employee_id, e.last_name  
ORDER BY all_customers DESC, employee_last_name;