implementing a divide query in postgresql

Question

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)
                    )

Your except logic will always return a row. The except query will return at most one row for each A row, and there are two different values in branch_name. — Mike Organek
– Mike Organek, Commented Jul 13, 2022 at 19:22

Mike Organek · Accepted Answer · 2022-07-13 19:35:27Z

1

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

answered Jul 13, 2022 at 19:35

Mike Organek

12.6k3 gold badges15 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Martimc. Over a year ago

now i finally understand what i did wrong, since i was giving the branch_name and not the account_number, even in the correct number it was only making a set with only 1 city which was wrong, thanks a lot really

Mike Organek Over a year ago

@Martimc. My pleasure. You were also selecting from account rather than city in your first query. That increases the number of rows considered in the minuend of the except operation.

Tim Biegeleisen · Accepted Answer · 2022-07-13 19:00:45Z

1

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);

answered Jul 13, 2022 at 19:00

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

3 Comments

Martimc. Over a year ago

I agree that its simpler but the objective is to implement a divide thats why im doing the query like that

Tim Biegeleisen Over a year ago

What does "implement a divide" mean here?

Martimc. Over a year ago

Divide account with city returning all the accounts that have a branch_name in all the cities in city, u know divide is a set operation in relational algebra

Collectives™ on Stack Overflow

implementing a divide query in postgresql

2 Answers 2

2 Comments

3 Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

3 Comments

Related