SELECT Menu.Numero,COUNT(*) AS RecordCount FROM Menu INNER JOIN MenuEntity ON Menu.Numero=MenuEntity.NoMenu INNER JOIN MenuPrivilege ON Menu.Numero=MenuPrivilege.NoMenu INNER JOIN MenuRole ON Menu.Numero=MenuRole.NoMenu INNER JOIN Member ON MenuEntity.NoEntity=Member.NoEntity AND MenuPrivilege.NoType=Member.NoType AND MenuRole.NoRole=Member.NoRole WHERE Menu.Numero NOT IN (SELECT Menu.Numero FROM MenuPrivilegeExceptionCase INNER JOIN Menu ON MenuPrivilegeExceptionCase.NoMenu=Menu.Numero WHERE MenuPrivilegeExceptionCase.NoMember=Member.Numero) GROUP BY Menu.Numero UNION SELECT Menu.Numero,COUNT(*) AS RecordCount FROM Menu INNER JOIN MenuPrivilegeExceptionCase ON Menu.Numero=MenuPrivilegeExceptionCase.NoMenu AND MenuPrivilegeExceptionCase.Enabled=1 INNER JOIN MenuEntity ON Menu.Numero=MenuEntity.NoMenu INNER JOIN Member ON MenuEntity.NoEntity=Member.NoEntity WHERE MenuPrivilegeExceptionCase.NoMember=Member.Numero GROUP BY Menu.NumeroThis gets me two records for each group by. Each two-record set contains a count for the first part of the SQL command and another count for the second part of the SQL command. So, I can obtain something like this:
UPDATE Menu SET Count=Temp.RecordCount FROM Menu JOIN (SELECT Menu.Numero,COUNT(*) AS RecordCount FROM Menu INNER JOIN MenuEntity ON Menu.Numero=MenuEntity.NoMenu INNER JOIN MenuPrivilege ON Menu.Numero=MenuPrivilege.NoMenu INNER JOIN MenuRole ON Menu.Numero=MenuRole.NoMenu INNER JOIN Member ON MenuEntity.NoEntity=Member.NoEntity AND MenuPrivilege.NoType=Member.NoType AND MenuRole.NoRole=Member.NoRole WHERE Menu.Numero NOT IN (SELECT Menu.Numero FROM MenuPrivilegeExceptionCase INNER JOIN Menu ON MenuPrivilegeExceptionCase.NoMenu=Menu.Numero WHERE MenuPrivilegeExceptionCase.NoMember=Member.Numero) GROUP BY Menu.Numero UNION SELECT Menu.Numero,COUNT(*) AS RecordCount FROM Menu INNER JOIN MenuPrivilegeExceptionCase ON Menu.Numero=MenuPrivilegeExceptionCase.NoMenu AND MenuPrivilegeExceptionCase.Enabled=1 INNER JOIN MenuEntity ON Menu.Numero=MenuEntity.NoMenu INNER JOIN Member ON MenuEntity.NoEntity=Member.NoEntity WHERE MenuPrivilegeExceptionCase.NoMember=Member.Numero GROUP BY Menu.Numero) AS Temp ON Temp.Numero=Menu.NumeroBut, that did not give me the expected result as it updates the Menu.Count field but with only the first count. There has to be something simple to adjust here to obtain the desired result.