Mohammad-Ali Itani Mohammad-Ali Itani - 4 months ago 12
SQL Question

MySQL - Get count of users that have a "user-contact"

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.



Requirements:

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".


E.g., 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.

Answer

Try this:

SELECT c.country_id,
       COUNT(DISTINCT CASE WHEN u2.user_id IS NOT NULL THEN u.user_id END) as has_contact_that_is_user ,
       COUNT(distinct CASE WHEN u2.user_id is null AND u.user_id IS NOT NULL THEN u.user_id END) as has_no_contact_that_is_user 
FROM Country c
LEFT JOIN users u
 ON(c.country_id = u.country_id)
LEFT JOIN contacts co
 ON(co.user_id = u.user_id)
LEFT JOIN Users u2
 ON(u2.user_msisdn = co.contact_msisdn)
 GROUP BY c.country_id

Fiddle