Hcfyk

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?

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.

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.

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

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