SELECT
  BP.ClientBranchProductID,
  CP.ClientProductID,
  CB.ClientBranchID,
  CB.ClientID,
  CP.ProductID,
  CP.JoinDate
FROM
  taClientBranchProducts AS BP
    LEFT JOIN taClientProducts AS CP ON (CP.ClientProductID = BP.ClientProductID)
    LEFT JOIN taClientBranches AS CB ON (CB.ClientBranchID = BP.ClientBranchID)
WHERE
  (BP.ClientBranchProductID = @nClientBranchProductID)