Consider the three table in the following Schema SQL Fiddle.
- This mini-model represents a simple example of a database I'm working on .
- The Users table has 4 attributes, the user_id (primary key auto_increment), the country_id which is a foreign key referencing the Country table, the user_msisdn which is the phone number of the user, and the username.
- The Contacts table has 3 attributes, the contact_id (primary key auto_increment), the user_id which is a foreign key referencing the Users table, and the contact_msisdn which is the user contact's phone numbers (the phone numbers on your phone contacts list).
- The relation between the Users table and the Contacts table is many-to-many, a user can have many contacts, and a contact can be found in any user's contacts list.
For each country, get the count of users that have at least one "user-contact", where a "user-contact" is a contact that is a user, and the count of users that don't have any "user-contact".
For country = 'Sweden' (country_id = 3), the user_id = 3 has two contacts in the Contacts
table that are considered "user-contact" with (msisdn = '+220011223344' & '+224433221100'). So the query results that I want: Sweden has 1 user (user_id = 3) that has at least one "user-contact" and zero users that have no "user-contact", and so on for each country.