Получить идентификатор левой стороне таблицы на основе условия на правой стороне таблицы в SQL?

Полагаю, вы должны добавить условия как

SELECT NIH.NewItemHeaderId,NIH.TPSPStatus AS 'Header Status', 
NIR.NewItemRequestId,NIR.Status AS RequesStatus
FROM NewItemHeader NIH
LEFT JOIN NewItemRequest NIR ON NIR.NewItemHeaderId = NIH.NewItemHeaderId
WHERE NIH.TPSPStatus = 'MRD - Standardizer Pending' 
AND NIR.Status = 'MRD - Standardizer Pending'