SELECT
  A.ClientBranchID,
  A.BranchName
FROM
  taClientBranches AS A
WHERE
  (A.ClientID = @nClientID) AND
  (A.BranchStatus = 1) AND
  (A.ClientBranchID NOT IN (
    SELECT
      CB.ClientBranchID
    FROM
      taClientBranches AS CB
        LEFT JOIN taClientBranchProducts AS BP ON (BP.ClientBranchID = CB.ClientBranchID)
        LEFT JOIN taClientProducts AS CP ON (CP.ClientProductID = BP.ClientProductID)
    WHERE
      (CB.ClientID = @nClientID) AND
      (CB.BranchStatus = 1) AND
      (BP.ClientProductID = @nClientProductID)
  ))
