Getting only one row of data per primary key when there are many foreign keys that equals it

I have a procedure where I am trying to fetch the only on row of data per foreign key (FK) per (PK) in a table (turning a one-to-many relationship into 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 returns the wrong entries:

I have multiple entries per company:

but I'm also missing some entries:

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

You want exactly one employee per company, and only the most recent manager hired. You can figure out the latter two using a TOP (1) inside a correlated APPLY.

SELECT c.CompanyName, e.* FROM Company AS c
  OUTER APPLY 
  (SELECT TOP (1) EmployeeName, DateHired
    FROM Employee AS e
  INNER JOIN JobRole AS j
  ON e.JobRoleNo_FK = j.JobRoleNo_PK
  WHERE j.RoleName = N'Manager'
    AND e.CompanyNo_FK = c.CompanyNo_PK
  ORDER BY DateHired DESC) AS e;

Output:

• fiddle

This will include companies that don't have any managers (if you want to filter those out, use CROSS APPLY instead of OUTER APPLY).

If you know the job role number already, or have already passed it in from somewhere, you can simplify a little:

SELECT c.CompanyName, e.* FROM Company AS c
  OUTER APPLY (SELECT TOP (1) EmployeeName, DateHired
    FROM Employee AS e
  WHERE e.JobRoleNo_FK = 1
    AND e.CompanyNo_FK = c.CompanyNo_PK
  ORDER BY DateHired DESC) AS e;

You can use a CTE approach too, I just find it a lot more complicated to write:

WITH cte AS
(
  SELECT c.CompanyName, e.EmployeeName, e.DateHired, 
      LatestRow = ROW_NUMBER() OVER 
        (PARTITION BY c.CompanyNo_PK 
         ORDER BY e.DateHired DESC)
    FROM Company AS c
    LEFT OUTER JOIN Employee AS e
    ON c.CompanyNo_PK = e.CompanyNo_FK
    AND e.JobRoleNo_FK = 1 -- or @JobRoleNo param
  )
SELECT CompanyName, EmployeeName, DateHired
  FROM cte
  WHERE LatestRow = 1;

• updated fiddle