implementing a divide query in postgresql-CodePudding

Basically i have these relations, account(account_number,branch_name,balance) and city(branch_name,country). What i want is all the account_numbers of the people who have accounts on all the cities in the city table but for some reason im getting an empty query and i dont get why.

Ps: ive seen some confusion on what the divide means, the divide is the division of 2 sets like in relational algebra, what i want is to divide the account set with the city set, in doing so, it is supposed to return all accounts that have a branch_name in every city in cities

Example:

           account table
--------------|-----------|---------
account_number|branch_name|balance
--------------|-----------|---------
 'A-000000'   |'Downtown' | 3467
 'A-000001'   |'Downtown' | 1500
 'A-000002'   |'London'   | 1500
 'A-000826'  |'Manchester'| 9999999
 'A-000826'   |'Downtown' | 33399
------------------------------------


               city
--------------|-----------
  branch_name | country
--------------|-----------
 'Manchester' | 'UK'
 'Downtown'   | 'USA'
--------------------------

           Account ÷ city
--------------|-----------|---------
account_number|branch_name|balance
--------------|-----------|---------
 'A-000826'  |'Manchester'| 9999999
 'A-000826'   |'Downtown' | 33399
-----------------------------------

'A-000826' is the only account in all of the cities in the city table

Program:

create table account(
    account_number  char(9),
    branch_name     varchar(80) not null,
    balance         numeric(16,4));
    
create table city(
    branch_name     varchar(80) not null,
    country         varchar(80) not null,
    check(branch_name != ''));

INSERT INTO city(branch_name,country)
VALUES ('Manchester','UK'),
('Downtown','USA');


INSERT INTO account(account_number, branch_name, balance) 
VALUES ('A-000000','Downtown',3467),
('A-000001','Downtown',1500),
('A-000002','London',1500),
('A-000826','Manchester',9999999),
('A-000826','Downtown',33399);

Query:

select *
from account as A
where not exists ( (select branch_name
                    from account)
                    except 
                    (select C.branch_name
                     from city as C
                     where A.branch_name = C.branch_name)
                    )

CodePudding user response：

I agree with Tim Biegeleisen's answer.

Your logic in the where not exists was backwards.

If you insist on sticking to relational algebra, then try this, instead:

select *
  from account as A
 where not exists (
         select branch_name
           from city
         except
         select branch_name
           from account b
          where b.account_number = a.account_number
       );

db<>fiddle here

CodePudding user response：

I would use an aggregation approach:

SELECT a.account_number
FROM account a
INNER JOIN city c
    ON c.branch_name = a.branch_name
GROUP BY a.account_number
HAVING COUNT(*) = (SELECT COUNT(*) FROM city);