CODEWITHSUNDEEP
sql
Di Wang
Di Wang

Reputation: 481

SQL: find customers who own accounts in all of the branches that 'Adam Rivera' owns accounts in

I need To find The Customer ID, last name and birth dates of customers who own accounts in All of the branches that Adam Rivera owns accounts in.

This is the bank schema:

§ Customer = {customerID, firstName, lastName, income, birthDate }
§ Account = {accNumber, type, balance, branchNumberFK-Branch}
§ Owns = {customerIDFK-Customer, accNumberFK-Account}
§ Transactions = {transNumber, accNumberFK-Account, amount}
§ Employee = {sin, firstName, lastName, salary, branchNumberFK-Branch}
§ Branch = {branchNumber, branchName, managerSINFK-Employee, budget}

All branch numbers that Adam Rivera owns, The query gives:

branchNumber
1
4

SELECT b2.branchNumber -- all branches that Adam Rivera owns
FROM Customer c2 join Owns o2 on c2.customerID = o2.customerID join Account a2 on a2.accNumber = o2.accNumber join Branch b2 on b2.branchNumber = a2.branchNumber
WHERE c2.firstName = 'Adam' and c2.lastName = 'Rivera'

A list of branch numbers that a customer has an account at Adam Rivera's branches, The reuslt is :

cusID   lstName birthDate   branch#
10839   Hayes   1977-06-09  1
10839   Hayes   1977-06-09  4
11790   Green   1920-11-19  1
11790   Green   1920-11-19  1
13230   Brooks  1967-05-04  1
13423   Simmons 1955-03-21  1
13423   Simmons 1955-03-21  4
13697   Hill    1951-03-07  1
13697   Hill    1951-03-07  4
14295   Ramirez 1983-06-30  4
18166   Barnes  1951-03-07  1
19973   Kelly   1987-07-15  1
22050   Sanchez 1935-01-08  1
.......


SELECT c1.customerID, c1.lastName, c1.birthDate, b1.branchNumber

FROM Customer c1 join Owns o1 on c1.customerID = o1.customerID join Account a1 on a1.accNumber = o1.accNumber join Branch b1 on b1.branchNumber = a1.branchNumber

WHERE EXISTS 
      SELECT b3.branchNumber
      FROM Customer c3 join Owns o3 on c3.customerID = o3.customerID join Account a3 on a3.accNumber = o3.accNumber join Branch b3 on b3.branchNumber = a3.branchNumber
      WHERE c3.firstName = 'Adam' and c3.lastName = 'Rivera' and b1.branchNumber = b3.branchNumber

The following result should return all of the branches that a customer does not have an account at, if the customer has an account at every branch then this result is empty. 

WHERE NOT EXISTS
    (branch numbers that Adam Rivera owns) 
    EXCEPT 
    (a list of branch numbers that a customer has account at Adam Rivera's branches)

Using NOT EXIST after WHERE, should return a customer who owns all Adam Rivera's account

This is the query I wrote.

SELECT c1.customerID, c1.lastName, c1.birthDate

FROM Customer c1 join Owns o1 on c1.customerID = o1.customerID join Account 
a1 on a1.accNumber = o1.accNumber join Branch b1 on b1.branchNumber = 
a1.branchNumber

WHERE NOT EXISTS

  (-- all branch numbers that Adam Rivera owns
  (SELECT b2.branchNumber 
  FROM Customer c2 join Owns o2 on c2.customerID = o2.customerID join 
Account a2 on a2.accNumber = o2.accNumber join Branch b2 on b2.branchNumber = a2.branchNumber
  WHERE c2.firstName = 'Adam' and c2.lastName = 'Rivera') 

  EXCEPT

  -- a list of branch numbers that a customer has account at Adam Rivera's branches
  (SELECT b3.branchNumber 
  FROM (Customer c3 join Owns o3 on c3.customerID = o3.customerID join Account a3 on a3.accNumber = o3.accNumber join Branch b3 on b3.branchNumber = a3.branchNumber)
  WHERE c3.firstName = 'Adam' and c3.lastName = 'Rivera' and b1.branchNumber = b3.branchNumber )
  )

The query should return Hayes, Simmons, Hill. But the result returns nothing(error). I can not figure out what is wrong here.

Upvotes: 0

Views: 1215

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270401

Here is one method:

with ar as (
      select distinct a.branchNumber
      from Customer c join
           Owns o 
           on c.customerID = o.customerID join
           Account a
           on a.accNumber = o.accNumber 
      where c.firstName = 'Adam' and c.lastName = 'Rivera'
     )
select o.customerId
from Owns o 
     Account a
     on a.accNumber = o.accNumber join
     ar
     on ar.branchNumber = a.branchNumber
group by o.customerId
having count(distinct a.branchNumber) = (select count(*) from ar);

The subquery are the accounts that Mr. Rivera owns. The join matches each account. The group by and having count the number of distinct branches for each customer, and check that the number matches Mr. Rivera's number.

Upvotes: 1

Related Questions