SQL specific question - get child that belong to all item of a collection

The schema is here :

GROUP 1 ----> * USER 1 ----> 1 PERSON 1 ---> * ADRESSE 1 -----> 1 CITY

I would like to get only City that belong to all Users

is it possible natively with sql or do i need to do it iteratively.

To say in a simple way : i just want to have a list of city where each user of the group has a relation with ....

Don't focus on real life, in this model user can have thousand cities ...

i can try to draw it (maybe simpler)

GROUP 1 -----> USER 1 -> PERSON 1-> ADRESSE 1 -> CITY 1
        -----> USER 2 -> PER开发者_C百科SON 2-> ADRESSE X -> CITY 1
        -----> USER 2 -> PERSON 2-> ADRESSE Y -> CITY 2
        -----> USER 3 -> PERSON 3-> ADRESSE Z -> CITY 1

I want only CITY 1

---

Does this get you started?

Select cityname, count(distinct userID) 
from  User u inner join Person p on  u.personID = p.personID 
inner join Adresse a on  p.addressID = a.addressID
inner join City c on a.cityID = p.cityID 
group by cityName

---

Assuming you have ID fields in each table that tie them together... using this type of where clause will return only the rows that have a city...

Select * 
from Group, User, Person, Adresse, City 
where city.cityID = addresse.cityID 
and addresse.addressID = person.adresse.addressID 
and person.personID = user.personID 
and user.userID = group.userID