Find countries by a user to city relationship where a user is related to all cities of a country

Question

I have four tables: User, Country, Region and City

Country has many Regions which has many Cities.

A user can be related to 0-to-many Cities (wish to visit).

How can I write a MySQL query (or DQL) that finds out Regions and Countries that a user is fully related to them, i.e wish to visit ALL of their Cities?

Answer 1

Use NOT EXISTS and LEFT JOIN

SELECT *
FROM Region r
WHERE NOT EXISTS(
   SELECT 1 
   FROM City c
   LEFT JOIN User u ON c.id_city = u.id_city and u.id_user = 'user_id'
   WHERE c.id_reg = r.id_reg and u.id_user IS NULL
)

This will find only regions where user wish to visit all cities, however, if you want countries it is just a slight modification

SELECT *
FROM Country ctr
WHERE NOT EXISTS(
   SELECT 1 
   FROM Region r
   LEFT JOIN City c ON r.id_reg = c.id_reg
   LEFT JOIN User u ON c.id_city = u.id_city and u.id_user = 'user_id'
   WHERE ctr.id_country = r.id_country and u.id_user IS NULL
)