'How to work with NOT IN using CTE in sql query?

I am having an issue with using NOT IN with cte. Actually, I want to display also a user who does not have any person_id in AccountOf table. But the statement after NOT IN is not executing Can anyone help me, please?

with p as (
        select p.id, p.name, a.person_id, Sum(b.balance) balance
        from persons p
        join accountOf a on a.person_id = p.Id
        join BankAccounts b on b.Id = a.account_id
        group by p.name
    )
    select *
    from p
    where p.balance < (select Max(balance) from p) * 0.5 OR p.id NOT IN (select person_id from p);

The 3 tables are:

Persons

id | name | address | age | eyeColor | gender

BankAccounts

id | balance

AccountOf

id | person_id → Persons | account_id → BankAccounts
mysqlsql


Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source

Related Questions