SELECT
  BP.ClientBranchProductID,
  CS.FinNumber,
  CS.GroupName,
  CONCAT(PR.ProductCategory, ' - ', PR.ProductName) AS ProductName,
  CB.BranchName,
  fnClientBranchProductRetainer(BP.ClientBranchProductID, NOW()) AS Retainer
FROM
  taClientBranchProducts AS BP
    LEFT JOIN taClientProducts AS CP ON (CP.ClientProductID = BP.ClientProductID)
    LEFT JOIN taClientBranches AS CB ON (CB.ClientBranchID = BP.ClientBranchID)
    LEFT JOIN taProducts AS PR ON (PR.ProductID = CP.ProductID)
    LEFT JOIN taProductCategory AS PC ON (PC.ProductCategory = PR.ProductCategory)
    LEFT JOIN taClients AS CS ON (CS.ClientID = CP.ClientID)
    LEFT JOIN taUsers AS U1 ON (U1.UserID = BP.ConsultantID)
WHERE
  (BP.Status = 1) AND
  (COALESCE(PC.MustHaveParent, 0) = 0) AND
  (BP.ConsultantID = @nUserID)
ORDER BY
  CS.GroupName,
  PR.ProductName,
  CB.BranchName