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

Question

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

CompanyNo_PK	CompanyName
1	Foo
2	Bar
3	FooBar

JOB ROLE

JobRoleNo_PK	RoleName
1	Manager
2	IT
3	Clerk

EMPLOYEE

EmployeeNo_PK	EmployeeName	CompanyNo_FK	JobRoleNo_FK	DateHired
1	Steve	1	1	1/3/2018
2	Bob	2	1	1/9/2021
3	Daisy	3	1	3/7/2020
4	John	1	2	2/9/2019
5	Sam	1	3	5/17/2022
6	Kathy	1	1	6/25/2022
7	Cait	3	3	9/6/2020
8	Adam	3	1	8/3/2021
9	Mario	2	2	4/30/2020

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

CompanyName	EmployeeName	DateHired
Foo	Kathy	6/25/2022
Bar	Bob	1/9/2021
FooBar	Adam	8/3/2021

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:

CompanyName	EmployeeName	DateHired
Foo	Kathy	6/25/2022
Bar	Bob	1/9/2021
FooBar	Daisy	3/7/2020

I have multiple entries per company:

CompanyName	EmployeeName	DateHired
Foo	Kathy	6/25/2022
Bar	Bob	1/9/2021
FooBar	Adam	8/3/2021
FooBar	Daisy	3/7/2020,

but I'm also missing some entries:

CompanyName	EmployeeName	DateHired
Bar	Bob	1/9/2021
FooBar	Adam	8/3/2021

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

Maybe this fiddle will help us collaborate on a solution dbfiddle.uk/qsl9BhzZ — Bart McEndree
– Bart McEndree, Commented Jun 17, 2024 at 19:36

score 2 · Accepted Answer · 2024-06-17 19:46:41Z

2

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:

CompanyName	EmployeeName	DateHired
Foo	Kathy	2022-06-25
Bar	Bob	2021-01-09
FooBar	Adam	2021-08-03

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

edited Jun 17, 2024 at 19:46

answered Jun 17, 2024 at 19:37

user25043454

Sign up to request clarification or add additional context in comments.

3 Comments

Bart McEndree Over a year ago

With "SET SHOWPLAN_XML ON" the CTE version seems to have a lower estimated subtree of cost 0.018 vs 0.040 for the OUTER APPLY version. dbfiddle.uk/GdynpGhf

user25043454 Over a year ago

@BartMcEndree Sure, but that difference is pretty easy to overcome with appropriate indexes: example.

Andrew C Over a year ago

The APPLY Operator solved my problem. Thank you!

Bart McEndree · Accepted Answer · 2024-06-17 19:46:40Z

1

Using Thom A suggestions from Get top 1 row of each group

WITH cte AS
(
   SELECT *,
         ROW_NUMBER() OVER (PARTITION BY c.CompanyNo_PK  ORDER BY e.[DateHired] DESC) AS rn
    FROM  
        [Company] c 
    INNER JOIN 
        [Employee] e ON e.CompanyNo_FK = c.CompanyNo_PK 
                     AND JobRoleNo_FK = 1
)
SELECT CompanyName,     EmployeeName,   DateHired
FROM cte
WHERE rn = 1

fiddle

CompanyName	EmployeeName	DateHired
Foo	Kathy	6/25/2022
Bar	Bob	1/9/2021
FooBar	Adam	8/3/2021

answered Jun 17, 2024 at 19:46

Bart McEndree

3,6041 gold badge12 silver badges20 bronze badges

Comments

Nick Straguzzi · Accepted Answer · 2024-06-17 19:56:55Z

This is a little simpler, or at least uses more basic constructs only - no CTE's, no ROW_NUMBER/PARTITION OVER's, no APPLY's.

SELECT c.CompanyName, e.EmployeeName, e.DateHired
  FROM Company c
  LEFT JOIN Employee e
       ON e.CompanyNo_FK = c.CompanyNo_PK AND e.JobRoleNo_FK = 1
 WHERE NOT EXISTS (
    SELECT 1
      FROM #Employee e1
     WHERE e1.CompanyNo_FK = c.CompanyNo_PK
       AND e1.JobRoleNo_FK = 1
       AND e1.DateHired > e.DateHired);

To be clear though, this won't really cover the case where a company has no managers at all; the company will be unlisted. If you want to avoid that and ensure that every company is represented in the final table, you'll need an OR clause after all (e.EmployeeNoPK IS NULL OR NOT EXISTS...)

Collectives™ on Stack Overflow

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

3 Answers 3

3 Comments

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

Comments

Linked

Related