How to use refer to multiple id's when using JOINs in SQL-CodePudding

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

CodePudding user response：

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 ...