Get all rows based on condition from another table-CodePudding

I have 3 tables.

AccountInCompany
Account
Id | Name
Company

Backstory: The accounts can be part of multiple companies at the same time. And that's the purpose of AccountInCompany, to keep track of it.

What I want to do is get all accounts who don't are not part of any enabled companies. I tried so many different queries and I couldn't get it to work. For example:

AccountInCompany:

`Id`	`AccountId`	`CompanyId`
1	1	1
2	1	2
3	1	3
4	2	1
5	3	4
6	4	3
7	4	4

Company:

`Id`	`IsEnabled`
1	'True'
2	'True'
3	'False'
4	'False'
5	'False'

What it means in this case: the account (1) should NOT be selected, because even though company (3) is not enabled, he is still part of companies (1 and 2) which are enabled. Account 2 should be skipped as well, because the company he's part of (1) is enabled. Account 3 should be selected because its only company (4) is disabled. Also account 4 should be selected because both its companies 3 and 4 are also disabled.

Some of my tries include:

WITH OnlyDisabled AS (
  SELECT DISTINCT u.Id
        FROM AccountInCompany ac
       INNER JOIN Account a
          ON a.Id = ac.AccountId
       INNER JOIN Company c
          ON c.Id = ac.CompanyId
       WHERE c.IsEnabled = 'False'),
    FinalList AS (
  SELECT du.Id
        FROM OnlyDisabled du
    WHERE NOT EXISTS (SELECT * FROM AccountInCompany ac
       INNER JOIN Account a
          ON a.Id = ac.AccountId
       INNER JOIN Company c
          ON c.Id = ac.CompanyId
       WHERE a.IsEnabled = 'True'
         AND ac.AccountId IN (SELECT * FROM OnlyDisabled)))
SELECT * FROM FinalList;

Another using ALL:

SELECT DISTINCT u.Id
        FROM AccountInCompany ac
       INNER JOIN Account a
          ON a.Id = ac.AccountId
       INNER JOIN Company c
          ON c.Id = ac.CompanyId
       WHERE c.IsEnabled = ALL (SELECT IsEnabled FROM Company WHERE IsEnabled = 'True')

Also tried to make it work with GROUP BY, but no luck either. Any help is appreciated!

CodePudding user response：

We connect the tables, group by account and then dismiss any accounts with status = 'true'

select   account
from     AccountInCompany aic 
join     Company c on c.company = aic.company
group by account
having   max(status) <> 'True'

account
3
4

Fiddle