I have some trouble trying to display the results of numbers of users I have for each city of my DB.
Users by default are listed to every city of the database but they can unmark some. The tables and their relations would be something like this:
Table user:
| user_id | state_id |
| -------- | -------------- |
| 1 | 1 |
| 2 | 1 |
| 3 | 1 |
Table state
| state_id | name |
| -------- | -------------- |
| 1 | foo |
Table city
| city_id | state_id | city_name |
| -------- | -------------- | --------------- |
| 1 | 1 | a |
| 2 | 1 | b |
| 3 | 1 | c |
| 4 | 1 | d |
Table city_exclusion
| user_id | city_id |
| -------- | -------------- |
| 1 | a |
| 1 | b |
| 2 | a |
| 3 | c |
So the expected result would be like this:
| city | count |
| -------- | -------------- |
| a | 1 |
| b | 2 |
| c | 2 |
| d | 3 |
I've been trying some stuff that the one bellow
SELECT U.city, COUNT(U.id) count
FROM user U
LEFT JOIN city C ON C.state_id = U.state_id
WHERE U.id NOT IN (
SELECT U.id
FROM city_exclusion
)
GROUP BY 1;
However, running that kind of queries excludes any user at "exclusion" table for all the registers .
How can I just clean the registers from a count? Is that with a subquery on in another way?
