SELECT
  BP.ClientBranchProductID,
  BP.ClientBranchID,
  BP.ClientProductID,
  CB.ClientID,
  CS.ClientStatus,
  CS.TerminationDate AS ClientTerminationDate,
  CP.ProductID,
  CP.TerminationDate AS ClientProductTerminationDate,
  BP.JoinDate,
  BP.Status,
  CB.LabourNetBranchID,
  P1.ClientProductID AS ParentClientProductID,
  IF(P1.ClientProductID IS NULL, BP.ConsultantID, P2.ConsultantID) AS ConsultantID,
  IF(P1.ClientProductID IS NULL, BP.SalesReferrerID, P2.SalesReferrerID) AS SalesReferrerID,
  IF(P1.ClientProductID IS NULL, BP.SalesCloserID, P2.SalesCloserID) AS SalesCloserID,
  IF(P1.ClientProductID IS NULL, BP.TechnicalSpecialistID, P2.TechnicalSpecialistID) AS TechnicalSpecialistID,
  ROUND(COALESCE(PF.RetainerFee, 0.0), 2) AS CurrentRetainer,
  BP.TerminationDate,
  BP.TerminationReason,
  BP.TerminationNotes
FROM
  taClientBranchProducts AS BP
    LEFT JOIN taClientBranches AS CB ON (CB.ClientBranchID = BP.ClientBranchID)
    LEFT JOIN taClientProducts AS CP ON (CP.ClientProductID = BP.ClientProductID)
    LEFT JOIN taClients AS CS ON (CS.ClientID = CB.ClientID)
    LEFT JOIN taProducts AS PR ON (PR.ProductID = CP.ProductID)
    LEFT JOIN taClientProductFees AS PF ON (PF.ClientProductFeeID = (

      SELECT MAX(M.ClientProductFeeID)
      FROM taClientProductFees AS M
      WHERE
        (M.ClientBranchProductID = @nClientBranchProductID) AND
        (M.StartDate = (

          SELECT MAX(N.StartDate)
          FROM taClientProductFees AS N
          WHERE
            (N.ClientBranchProductID = @nClientBranchProductID) AND
            (N.StartDate <= LEFT(NOW(), 10))

        ))

    ))
    LEFT JOIN taClientProducts AS P1 ON (P1.ClientProductID = CP.ParentClientProductID)
    LEFT JOIN (

      SELECT DISTINCT
        BP.ClientProductID,
        BP.ClientBranchID,
        BP.ConsultantID,
        BP.SalesReferrerID,
        BP.SalesCloserID,
        BP.TechnicalSpecialistID,
        BP.JoinDate
      FROM
        taClientBranchProducts AS BP
          LEFT JOIN taClientProducts AS CP ON (CP.ClientProductID = BP.ClientProductID)
      WHERE
        (BP.Status = 1) AND
        ((CP.ParentClientProductID IS NULL) OR (CP.ParentClientProductID <= 0))

    ) AS P2 ON ((P2.ClientProductID = CP.ParentClientProductID) AND (P2.ClientBranchID = BP.ClientBranchID))
WHERE
  (BP.ClientBranchProductID = @nClientBranchProductID)
