I have a procedure where I am trying to fetch the only on row of data per Foreign Key (FK) per (PR) in a table (turing one-to-many relationships to a one-to-one table).
I have some tables that looks like below. (NOTE: My tables do not actually look like this, these are just examples.)

COMPANY

JOB ROLE

EMPLOYEE

My procedure should output a table using the tables above to only see the MOST CURRENT Manager for each Company. So the output should look something like this:

CURRENT COMPANY MANAGERS

I tried the following query below, but I did not get the result I wanted.

CREATE PROCEDURE [Current Company Managers] AS
SELECT DISTINCT c.[CompanyName], e.[EmployeeName], e.[DateHired]
FROM [Company] c 
LEFT JOIN [Employee] e ON e.CompanyNo_FK = c.CompanyNoPK AND JobRoleNo_FK = 1
    AND (NOT EXISTS (SELECT 1 FROM [Employee] ee 
        WHERE c.CompanyNo_PK = ee.CompanyNo_FK AND ee.DateHired > e.DateHired)
    OR NOT EXISTS (SELECT 1 FROM [Employee] ee 
        WHERE c.CompanyNo_PK = ee.CompanyNo_FK AND ee.DateHired < e.DateHired))

I included the OR NOT EXISTS operation since there were cases where some current managers were not being listed in the view when they should be or the managers being listed were incorrect (mainly the older entries). However, even with that, the results would:

list the wrong entries,

have multiple entries per Company,

be missing some entries,

or some combination of the three. So explanation for why this is happening will be much appreciated.