3

I have 3 tables as follow:

Customer (CustomerName , CustomerCity , CustomerStreet)
Branch   (BranchName , BranchCity)
Deposit  (CustomerName , BranchName , AccountNumber , Balance)

Here I show you sample data of the above tables :

Customer ('John','City1','Street1')
         ('Lili','City2','street2')

Branch   ('Branch1','Kaj')
         ('Branch2','Bahar')
         ('Branch3','Bahar')
         ('Branch4','Bahar')

Deposit  ('John','Branch1',12,1000)
         ('Lili','Branch1',123,1222)
         ('Lili','Branch2',124,45233)
         ('Lili','Branch3',345,6000)
         ('Lili','Branch4',567,5600)

So based on the information above, I need a query to give me CustomerName that have a deposit in all branches that are in Bahar.

I should see 'Lili' in the output because it has deposits for all branches that are in Bahar but I should not see John cause it has a deposit only in one branch of Bahar.

I did my best to write the query but I could not. Could you help me with this please?

6

I have two solutions for your question : solution 1 , I use Not Exists , Except

Select distinct C.CustomerName
From customers C
where NOT EXISTS ((Select BranchName
                   From Branch
                   Where BranchCity='Bahar')

                      EXCEPT

                   (Select BranchName
                    From Deposit a
                    where A.CustomerName = C.CustomerName))

In solution 2 I use Not Exists ,

Select Distinct C.CustomerName
From Customers C
Where NOT EXISTS  (Select BranchName
                   From Branch b
                   Where BranchCity='Bahar'
                   and NOT EXISTS (select BranchName
                                   from Deposit a
                                   where a.CustomerName=C.CustomerName
                                   and a.BranchName=b.branchName))

Each one of the query above will answer your query and give you the desired result.

0
select CustomerName
from Deposit
group by CustomerName
having count(distinct BranchName) = (select count(*) from Branch)

Note that distinct is only used if there are multiple deposits by the same customer and branch. However, it's possible to use join with Customer table or nested lookup queries for more information.

0
;with cte as(
  select c.Name as Cname,b.Name as bName,
  ROW_NUMBER() over(partition by c.Name order by b.Name,c.Name) RowID
  from Customer c 
  inner join Deposit d on c.Name=d.CustomerName
  inner join Branch b on b.Name=d.Branch
  where b.City='Bahar'
)
select Cname from cte 
group by cName
having max(RowID)=(select count(*) from Branch where City='Bahar')
-1
SELECT c.CustomerName
FROM Customer c
CROSS JOIN Branch b
LEFT JOIN Deposit d
ON d.CustomerName = c.CustomerName
AND d.BranchName = b.BranchName
WHERE b.BranchCity = 'Bahar'
GROUP BY c.CustomerName
HAVING SUM(CASE WHEN d.AccountNumber IS NULL THEN 1 ELSE 0 END) = 0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.