SELECT DISTINCT
  P2.ConsultantID,
  P2.SalesReferrerID,
  P2.SalesCloserID,
  P2.TechnicalSpecialistID,
  P2.JoinDate
FROM
  taClientProducts AS CP
    INNER JOIN taClientProducts AS P1 ON (P1.ClientProductID = CP.ParentClientProductID)
    INNER JOIN taClientBranchProducts AS P2 ON (P2.ClientProductID = P1.ClientProductID)
WHERE
  (CP.ClientProductID = @nChildClientProductID) AND
  (P2.ClientBranchID = @nChildClientBranchID) AND
  (P2.Status = 1)
