SELECT
  XE.XeroEntityID,
  XE.XeroEntityName,
  XE.InvoicePrefix,
  Y.ClientCount,
  XE.Enabled
FROM
  taXeroEntity AS XE
    LEFT JOIN (
      SELECT X.AccountBookID AS XeroEntityID, COUNT(*) AS ClientCount FROM (
        SELECT CP.AccountBookID, CP.ClientID FROM taClientProducts AS CP WHERE (CP.AccountBookType = 'Xero') AND (CP.Status = 1)
        UNION
        SELECT BM.AccountBookID, BM.ClientID FROM taAccountBookMember AS BM WHERE (BM.AccountBookType = 'Xero')
      ) AS X GROUP BY 1
    ) AS Y ON (Y.XeroEntityID = XE.XeroEntityID)
