create table #Orders
(
OrdNum varchar(5)
, Empid int
, Mgrid int
, MgrLevel int
)

insert #Orders
values	('XZ1', 100, 100, 1)
		, ('XZ1', 100, 351, 2)
		, ('XZ1', 100, 355, 3)
		
		, ('XZ1', 200, 200, 1)
		, ('XZ1', 200, 451, 2)
		, ('XZ1', 200, 555, 3)
		
		, ('SY1', 200, 200, 1)
		, ('SY1', 200, 451, 2)
		, ('SY1', 200, 555, 3)

SELECT a.*
FROM #Orders a
INNER JOIN (
    SELECT OrdNum, EmpID
    FROM #Orders
    WHERE OrdNum = 'XZ1' AND Mgrid = 555
    ) b
    ON a.OrdNum = b.OrdNum and a.EmpID = b.EmpID