Maybe simple – Change ID with real name from another table

I hope you can help a lost person in SQL 🙂

Making this SQL query:

SELECT c.CompanyName, c.ClientNumber, obo.EndMonth, ob.EndDate, ob.ClosedBy,
      ob.ClosedOn, obc.IsActive, obc.CheckPointTypeFk, cpt.Name
FROM OperationBase_Operation AS obo
INNER JOIN Person AS c ON obo.ClientFk = c.Id
INNER JOIN OperationBase_CheckPoint AS obc ON obo.Id = obc.OperationFk
INNER JOIN CheckPointType AS cpt ON obc.CheckPointTypeFk = cpt.Id
INNER JOIN OperationBase AS ob ON obc.Id = ob.Id    
WHERE obo.EndMonth = '202002'
ORDER BY c.CompanyName, ob.EndDate;

Results: Results image

QUESTION: The ID 329 under “ClosedBy” – I would like to change that ID with real name from the table Person – Find ID 329 in Person table and get the colum “Signature” on row 329

Person table image

Answer

You need another join of OperationBase to Person, but this time make it a left join because I see that there are nulls in your query and instead of ob.ClosedBy select Signature:

SELECT 
  c.CompanyName, c.ClientNumber, obo.EndMonth, ob.EndDate, p.Signature, 
  ob.ClosedOn, obc.IsActive, obc.CheckPointTypeFk, cpt.Name
FROM OperationBase_Operation AS obo
INNER JOIN Person AS c ON obo.ClientFk = c.Id
INNER JOIN OperationBase_CheckPoint AS obc ON obo.Id = obc.OperationFk
INNER JOIN CheckPointType AS cpt ON obc.CheckPointTypeFk = cpt.Id
INNER JOIN OperationBase AS ob ON obc.Id = ob.Id
LEFT JOIN Person AS p ON p.ID = ob.ClosedBy
WHERE obo.EndMonth = '202002'
ORDER BY c.CompanyName, ob.EndDate;