I try to display the number of reviews which an employee provided and the number of reviews which he received. I try to do something like this, but in my output table (3) I receive incorrect data. I believe that this happens due to the wrong reference:
pr.reviewer_id = e.employee_id
however, if I reference it like this:
pr.employee_id = e.employee_id
then nr_of_reviews_receieved
is correct and nr_of_reviews_posted
is not, and if I change it back it’s vice versa. So, I need in one case to use one reference and in another a different one, but all in one query.
SELECT e.employee_id, CONCAT_WS(' ',employee_first_name, employee_last_name) AS full_name, COUNT(pr.reviewer_id) AS nr_of_reviews_posted, COUNT(pr.employee_id) AS nr_of_reviews_received FROM employee AS e LEFT JOIN performance_review AS pr ON pr.reviewer_id = e.employee_id GROUP BY employee_first_name, employee_last_name, e.employee_id
Advertisement
Answer
You have to sample the tables twice and join to the table each time for example:
SELECT e.employee_id, CONCAT_WS(' ',employee_first_name, employee_last_name) AS full_name, COUNT(pr.reviewer_id) AS nr_of_reviews_posted, COUNT(pr2.employee_id) AS nr_of_reviews_received FROM employee AS e LEFT JOIN performance_review AS pr ON pr.reviewer_id = e.employee_id LEFT JOIN performance_review AS pr2 ON pr2.employee_id = e.employee_id GROUP BY Necessary columns ...