How to join a table with itself?

I want to ask how can I join the table with itself, using the same column twice, and giving each a different WHERE to sort out. I have an Employees table that contain Employee_name, job_id and manager_id. I want it to get the name of all the managers through the job_id on one column, and then all the employees that they manage on the other column. This is what I came up with :

SELECT A.LAST_NAME AS MANAGER_NAME, B.LAST_NAME AS EMPLOYEES_NAME
FROM EMPLOYEES A
RIGHT JOIN EMPLOYEES B
ON A.EMPLOYEE_ID = B.EMPLOYEE_ID
WHERE A.JOB_ID LIKE '%MAN%'
AND A.MANAGER_ID = B.MANAGER_ID

This simply put the manager name on both columns, and I don’t have another idea on how to do this. Any help would be greatly appreciated, thank you so much.

Answer

You join condition needs to change, the WHERE criteria should be moved to the ON clause, and also I would use a left join here:

SELECT emp1.LAST_NAME AS EMPLOYEES_NAME, COALESCE(emp2.LAST_NAME, 'NA') AS MANAGER_NAME
FROM EMPLOYEES emp1
LEFT JOIN EMPLOYEES emp2
    ON emp2.EMPLOYEE_ID = emp1.MANAGER_ID AND emp2.JOB_ID LIKE '%MAN%';

It is unclear whether you even need the check on the JOB_ID, but in any case I use COALESCE above to display NA as the manager name for any employee not having a manager.